package co.fitstart.admin.service.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.demo2do.core.persistence.GenericDaoSupport;
import com.demo2do.core.persistence.GenericJdbcSupport;
import com.demo2do.core.persistence.annotation.Dynamic;
import com.demo2do.core.support.Page;
import com.demo2do.core.utils.CollectionUtils;
import com.demo2do.core.utils.DateFormatUtils;
import com.demo2do.core.utils.DateUtils;

import co.fitstart.admin.dao.CourseDaoSupport;
import co.fitstart.admin.dao.CourseRoundResultDao;
import co.fitstart.admin.dao.CourseScheduleDao;
import co.fitstart.admin.dao.FitnessTagDao;
import co.fitstart.admin.dao.ReportDao;
import co.fitstart.admin.dao.UserDao;
import co.fitstart.admin.entity.report.CoachFinishedScheduleReport;
import co.fitstart.admin.entity.report.CoachFinishedScheduleStat;
import co.fitstart.admin.entity.report.FinishedCourseCount;
import co.fitstart.admin.entity.report.FinishedCourseReportStatistics;
import co.fitstart.admin.entity.report.FinishedCourseSchedule;
import co.fitstart.admin.entity.report.HourUnitPrice;
import co.fitstart.admin.entity.report.InbodyCourseRoundReport;
import co.fitstart.admin.entity.report.InbodyTargetReport;
import co.fitstart.admin.entity.report.RevenueReportStatistics;
import co.fitstart.admin.entity.report.SiteFinishedScheduleStat;
import co.fitstart.admin.entity.report.SiteHour;
import co.fitstart.admin.entity.report.SiteHourStat;
import co.fitstart.admin.entity.report.StudentCourseRoundStat;
import co.fitstart.admin.entity.report.StudentUnitPrice;
import co.fitstart.admin.entity.report.TasteStatEveryDay;
import co.fitstart.admin.entity.report.TasteStatSingle;
import co.fitstart.admin.entity.report.UnfinishedRoundChange;
import co.fitstart.admin.entity.report.UnfinishedRoundReport;
import co.fitstart.admin.entity.report.UnfinishedRoundStat;
import co.fitstart.admin.entity.search.SigninReportSearch;
import co.fitstart.admin.service.ReportService;
import co.fitstart.entity.course.Course;
import co.fitstart.entity.course.CourseType;
import co.fitstart.entity.course.round.CourseRound;
import co.fitstart.entity.course.round.CourseRoundAttribute;
import co.fitstart.entity.course.round.CourseRoundResult;
import co.fitstart.entity.course.round.Goal;
import co.fitstart.entity.course.round.RoundStatus;
import co.fitstart.entity.course.schedule.CourseSchedule;
import co.fitstart.entity.course.schedule.ScheduleStatus;
import co.fitstart.entity.courseware.CoursewareTag;
import co.fitstart.entity.courseware.fitnessgroup.CoursewareEditStatus;
import co.fitstart.entity.order.OrderStatus;
import co.fitstart.entity.order.OrderType;
import co.fitstart.entity.order.PaymentStatus;
import co.fitstart.entity.principal.Principal;
import co.fitstart.entity.report.FinishedCourseReport;
import co.fitstart.entity.report.RevenueReport;
import co.fitstart.entity.report.RevenueType;
import co.fitstart.entity.report.VisitorReport;
import co.fitstart.entity.site.Site;
import co.fitstart.entity.taste.TasteSignUp;
import co.fitstart.entity.taste.TasteStat;
import co.fitstart.entity.user.StudentTargetData;
import co.fitstart.entity.user.User;
import co.fitstart.entity.user.bwh.BwhRecord;
import co.fitstart.entity.user.inbody.InbodyRecord;

/**
 * 
 * @author dan
 *
 */
@Service("reportService")
public class ReportServiceImpl implements ReportService{

    @Dynamic
    private ReportDao reportDao;

    @Dynamic
    private CourseScheduleDao courseScheduleDao;

    @Autowired
    private GenericDaoSupport genericDaoSupport;

    @Autowired
    private CourseDaoSupport courseDaoSupport;

    @Autowired
    private GenericJdbcSupport genericJdbcSupport;

    @Dynamic
    private FitnessTagDao fitnessTagDao;

    @Dynamic
    private CourseRoundResultDao courseRoundResultDao;

    @Dynamic
    private UserDao userDao;

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listRevenueReport(co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public List<RevenueReport> listRevenueReport(Site site, Date date, String period, Page page) {
        
        // 1. get startTime and endTime by date and period
        Map<String, Date> times = this.getTimes(date, period);
        
        List<RevenueReport> result = new ArrayList<RevenueReport>();
        if(site == null) {
            result = reportDao.listRevenueReport(times.get("startTime"), times.get("endTime"), page.getBeginIndex(), page.getMaxResultRecords());
        } else {
            result = reportDao.listRevenueReport(site, times.get("startTime"), times.get("endTime"), page.getBeginIndex(), page.getMaxResultRecords());
        }
        page.calculate(result.size());
        
        return result.size() > page.getEveryPage() ? result.subList(0, page.getEveryPage()) : result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listRevenueReport(java.util.List, java.util.Date, java.lang.String, com.demo2do.core.support.Page)
     */
    public List<RevenueReport> listRevenueReport(List<Site> sites, Date date, String period, Page page) {
        Map<String, Date> times = this.getTimes(date, period);
        
        List<RevenueReport> result = reportDao.listRevenueReport(sites, times.get("startTime"), times.get("endTime"), page.getBeginIndex(), page.getMaxResultRecords());
        page.calculate(result.size());
        
        return result.size() > page.getEveryPage() ? result.subList(0, page.getEveryPage()) : result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listRevenueReport(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    public List<RevenueReport> listRevenueReport(Site site, Date startDate, Date endDate) {
        if(site == null) {
            return reportDao.listRevenueReport(
                    DateUtils.parseDate(DateFormatUtils.format(startDate, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"),
                    DateUtils.parseDate(DateFormatUtils.format(endDate, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        } else {
            return reportDao.listRevenueReport(site,
                    DateUtils.parseDate(DateFormatUtils.format(startDate, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"),
                    DateUtils.parseDate(DateFormatUtils.format(endDate, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        }
    }

    /**
     * 
     * @param date
     * @param period
     * @return
     */
    private Map<String, Date> getTimes(Date date, String period) {
        
        Map<String, Date> times = new HashMap<String, Date>();
        
        if(StringUtils.equalsIgnoreCase(period, "day")) {
            times.put("startTime", DateUtils.parseDate(DateFormatUtils.format(date, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
            times.put("endTime", DateUtils.parseDate(DateFormatUtils.format(date, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        } else if (StringUtils.equalsIgnoreCase(period, "week")) {
            times.put("startTime", DateUtils.parseDate(DateFormatUtils.format(DateUtils.getFirstWeekDay(date), "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
            times.put("endTime", DateUtils.parseDate(DateFormatUtils.format(DateUtils.getLastWeekDay(date), "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        } else if (StringUtils.equalsIgnoreCase(period, "month")) {
            times.put("startTime", DateUtils.parseDate(DateFormatUtils.format(DateUtils.getFirstMonthDay(date), "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
            times.put("endTime", DateUtils.parseDate(DateFormatUtils.format(DateUtils.getLastMonthDay(date), "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        }
        
        return times;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listRevenueReportStatistics(co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public Map<String, Map<String, Map<String, Long>>> listRevenueReportStatistics(Site site, Date date, String period) {
        
        
        Map<String, Date> times = this.getTimes(date, period);
        List<RevenueReport> reports = new ArrayList<RevenueReport>();
        if(site == null) {
            reports = reportDao.listRevenueReport(times.get("startTime"), times.get("endTime"));
        } else {
            reports = reportDao.listRevenueReport(site, times.get("startTime"), times.get("endTime"));
        }
        
        Map<String, Map<String, Map<String, Long>>> resultMap = this.generateResultMap();
        
        for(RevenueReport report : reports) {
            
            String revenueType = report.getRevenueType().getAlias();
            int pricePosition = report.getUnitPricePosition();
            int amountPosition = report.getUnitAmountPosition();
            String siteId = report.getSite().getId().toString();
            
            Map<String, Map<String, Long>> parentItems = resultMap.get(revenueType);
            
            if(pricePosition > 0) {
                
                long price = 0;
                
                Map<String, Long> items = parentItems.get(String.valueOf(pricePosition));
                
                if(items.get(siteId) != null) {
                    price = price + items.get(siteId);
                }
                price = price + report.getOffer();
                
                items.put(siteId, price);
                parentItems.put(String.valueOf(pricePosition), items);
            }
            
            if(amountPosition > 0) {
                
                long amount = 0;
                Map<String, Long> items2 = parentItems.get(String.valueOf(amountPosition));
                if(items2.get(siteId) != null) {
                    amount = amount + items2.get(siteId);
                }
                
                amount = amount + 1;
                
                items2.put(siteId, amount);
                parentItems.put(String.valueOf(amountPosition), items2);
            }
            
            
            resultMap.put(revenueType, parentItems);
        }
        
        return resultMap;
    }
    
    private Map<String, Map<String, Map<String, Long>>> generateResultMap() {
        
        Map<String, Map<String, Map<String, Long>>> resultMap = new HashMap<String, Map<String, Map<String, Long>>>();
        
        Map<String, Map<String, Long>> typeItems1 = new HashMap<String, Map<String, Long>>();
        typeItems1.put("1", new HashMap<String, Long>());
        typeItems1.put("2", new HashMap<String, Long>());
        typeItems1.put("3", new HashMap<String, Long>());
        typeItems1.put("4", new HashMap<String, Long>());
        typeItems1.put("5", new HashMap<String, Long>());
        typeItems1.put("6", new HashMap<String, Long>());
        typeItems1.put("7", new HashMap<String, Long>());
        typeItems1.put("8", new HashMap<String, Long>());
        typeItems1.put("9", new HashMap<String, Long>());
        typeItems1.put("10", new HashMap<String, Long>());
        typeItems1.put("11", new HashMap<String, Long>());
        typeItems1.put("12", new HashMap<String, Long>());
        typeItems1.put("13", new HashMap<String, Long>());
        typeItems1.put("14", new HashMap<String, Long>());
        typeItems1.put("15", new HashMap<String, Long>());
        typeItems1.put("31", new HashMap<String, Long>());
        typeItems1.put("33", new HashMap<String, Long>());
        typeItems1.put("35", new HashMap<String, Long>());
        typeItems1.put("37", new HashMap<String, Long>());
        
        typeItems1.put("16", new HashMap<String, Long>());
        typeItems1.put("17", new HashMap<String, Long>());
        typeItems1.put("18", new HashMap<String, Long>());
        typeItems1.put("19", new HashMap<String, Long>());
        typeItems1.put("20", new HashMap<String, Long>());
        typeItems1.put("21", new HashMap<String, Long>());
        typeItems1.put("22", new HashMap<String, Long>());
        typeItems1.put("23", new HashMap<String, Long>());
        typeItems1.put("24", new HashMap<String, Long>());
        typeItems1.put("25", new HashMap<String, Long>());
        typeItems1.put("26", new HashMap<String, Long>());
        typeItems1.put("27", new HashMap<String, Long>());
        typeItems1.put("28", new HashMap<String, Long>());
        typeItems1.put("29", new HashMap<String, Long>());
        typeItems1.put("30", new HashMap<String, Long>());
        typeItems1.put("32", new HashMap<String, Long>());
        typeItems1.put("34", new HashMap<String, Long>());
        typeItems1.put("36", new HashMap<String, Long>());
        typeItems1.put("38", new HashMap<String, Long>());
        
        Map<String, Map<String, Long>> typeItems2 = new HashMap<String, Map<String, Long>>();
        typeItems2.put("1", new HashMap<String, Long>());
        typeItems2.put("2", new HashMap<String, Long>());
        typeItems2.put("3", new HashMap<String, Long>());
        typeItems2.put("4", new HashMap<String, Long>());
        typeItems2.put("5", new HashMap<String, Long>());
        typeItems2.put("6", new HashMap<String, Long>());
        typeItems2.put("7", new HashMap<String, Long>());
        typeItems2.put("8", new HashMap<String, Long>());
        typeItems2.put("9", new HashMap<String, Long>());
        typeItems2.put("10", new HashMap<String, Long>());
        typeItems2.put("11", new HashMap<String, Long>());
        typeItems2.put("12", new HashMap<String, Long>());
        typeItems2.put("13", new HashMap<String, Long>());
        typeItems2.put("14", new HashMap<String, Long>());
        typeItems2.put("15", new HashMap<String, Long>());
        typeItems2.put("31", new HashMap<String, Long>());
        typeItems2.put("33", new HashMap<String, Long>());
        typeItems2.put("35", new HashMap<String, Long>());
        typeItems2.put("37", new HashMap<String, Long>());
        
        typeItems2.put("16", new HashMap<String, Long>());
        typeItems2.put("17", new HashMap<String, Long>());
        typeItems2.put("18", new HashMap<String, Long>());
        typeItems2.put("19", new HashMap<String, Long>());
        typeItems2.put("20", new HashMap<String, Long>());
        typeItems2.put("21", new HashMap<String, Long>());
        typeItems2.put("22", new HashMap<String, Long>());
        typeItems2.put("23", new HashMap<String, Long>());
        typeItems2.put("24", new HashMap<String, Long>());
        typeItems2.put("25", new HashMap<String, Long>());
        typeItems2.put("26", new HashMap<String, Long>());
        typeItems2.put("27", new HashMap<String, Long>());
        typeItems2.put("28", new HashMap<String, Long>());
        typeItems2.put("29", new HashMap<String, Long>());
        typeItems2.put("30", new HashMap<String, Long>());
        typeItems2.put("32", new HashMap<String, Long>());
        typeItems2.put("34", new HashMap<String, Long>());
        typeItems2.put("36", new HashMap<String, Long>());
        typeItems2.put("38", new HashMap<String, Long>());
        
        resultMap.put(RevenueType.PURCHASE.getAlias(), typeItems1);
        resultMap.put(RevenueType.REFUND.getAlias(), typeItems2);
        
        return resultMap;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listRevenueReportStatistics(java.util.List, java.util.Date, java.lang.String)
     */
    public Map<String, Map<String, Map<String, Long>>> listRevenueReportStatistics(List<Site> sites, Date date, String period) {
        
        Map<String, Date> times = this.getTimes(date, period);
        List<RevenueReport> reports = reportDao.listRevenueReport(sites, times.get("startTime"), times.get("endTime"));
        
        Map<String, Map<String, Map<String, Long>>> resultMap = this.generateResultMap();
        
        for(RevenueReport report : reports) {
            
            String revenueType = report.getRevenueType().getAlias();
            int pricePosition = report.getUnitPricePosition();
            int amountPosition = report.getUnitAmountPosition();
            String siteId = report.getSite().getId().toString();
            
            Map<String, Map<String, Long>> parentItems = resultMap.get(revenueType);
            
            if(pricePosition > 0) {
                
                long price = 0;
                
                Map<String, Long> items = parentItems.get(String.valueOf(pricePosition));
                
                if(items.get(siteId) != null) {
                    price = price + items.get(siteId);
                }
                price = price + report.getOffer();
                
                items.put(siteId, price);
                parentItems.put(String.valueOf(pricePosition), items);
            }
            
            if(amountPosition > 0) {
                
                long amount = 0;
                Map<String, Long> items2 = parentItems.get(String.valueOf(amountPosition));
                if(items2.get(siteId) != null) {
                    amount = amount + items2.get(siteId);
                }
                
                amount = amount + 1;
                
                items2.put(siteId, amount);
                parentItems.put(String.valueOf(amountPosition), items2);
            }
            
            resultMap.put(revenueType, parentItems);
        }
        
        return resultMap;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listRevenueReportStatistics(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    public Map<String, Map<String, Map<String, RevenueReportStatistics>>> listRevenueReportStatistics(Site site,
            Date startDate, Date endDate) {
        List<RevenueReportStatistics> result = new ArrayList<RevenueReportStatistics>();
        if(site == null) {
            result = reportDao.listRevenueReportStatistics(
                    DateUtils.parseDate(DateFormatUtils.format(startDate, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"),
                    DateUtils.parseDate(DateFormatUtils.format(endDate, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        } else {
            result = reportDao.listRevenueReportStatistics(site,
                    DateUtils.parseDate(DateFormatUtils.format(startDate, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"),
                    DateUtils.parseDate(DateFormatUtils.format(endDate, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        }
        
        return this.transferStatistics(result);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listRevenueReportStatistics(java.util.List, java.util.Date, java.util.Date)
     */
    public Map<String, Map<String, Map<String, RevenueReportStatistics>>> listRevenueReportStatistics(List<Site> sites, Date startDate, Date endDate) {
        List<RevenueReportStatistics> result = new ArrayList<RevenueReportStatistics>();
        result = reportDao.listRevenueReportStatistics(sites,
                    DateUtils.parseDate(DateFormatUtils.format(startDate, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"),
                    DateUtils.parseDate(DateFormatUtils.format(endDate, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return this.transferStatistics(result);
    }

    /**
     * 
     * @param result
     * @return
     */
    private Map<String, Map<String, Map<String, RevenueReportStatistics>>> transferStatistics(List<RevenueReportStatistics> result) {
        
        Map<String, List<RevenueReportStatistics>> reports = CollectionUtils.constructMapValuesFromList(result, "siteId");
        Map<String, Map<String, List<RevenueReportStatistics>>> revenueReports = new HashMap<String, Map<String, List<RevenueReportStatistics>>>();
        for (String key : reports.keySet()) {
            revenueReports.put(key, CollectionUtils.constructMapValuesFromList(reports.get(key), "courseId"));
        }
        
        Map<String, Map<String, Map<String, RevenueReportStatistics>>> revenueReportStatistics = new HashMap<String, Map<String, Map<String, RevenueReportStatistics>>>();
        for (String key : revenueReports.keySet()) {
            Map<String, Map<String, RevenueReportStatistics>> statistics = new HashMap<String, Map<String, RevenueReportStatistics>>();
            for(String reportKey : revenueReports.get(key).keySet()) {
                statistics.put(reportKey, CollectionUtils.constructMapValueFromList(revenueReports.get(key).get(reportKey), "revenueType.alias"));
            }
            revenueReportStatistics.put(key, statistics);
        }
        
        return revenueReportStatistics;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getRevenueTotal(java.util.Map)
     */
    public Map<String, Long> getRevenueTotal(Map<String, Map<String, Map<String, RevenueReportStatistics>>> statistics) {
        return this.calculateRevenueTotal(statistics);
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getRevenueTotal(java.util.List, java.util.Date, java.lang.String)
     */
    public Map<String, Long> getRevenueTotal(List<Site> sites, Date date, String period) {
        
        Map<String, Date> times = this.getTimes(date, period);
        List<RevenueReport> reports = reportDao.listRevenueReport(sites, times.get("startTime"), times.get("endTime"));
        
        long purchaseAmount = 0, purchaseTotal = 0, refundAmount = 0, refundTotal = 0;
        
        for(RevenueReport report : reports) {
            
            if(report.getRevenueType().equals(RevenueType.PURCHASE)) {
                purchaseAmount = purchaseAmount + 1;
                purchaseTotal = purchaseTotal + report.getOffer();
            } else if (report.getRevenueType().equals(RevenueType.REFUND)) {
                refundAmount = refundAmount + 1;
                refundTotal = refundTotal + report.getOffer();
            }
        }
        
        Map<String, Long> result = new HashMap<String, Long>();
        result.put("purchaseAmount", purchaseAmount);
        result.put("purchaseTotal", purchaseTotal);
        result.put("refundAmount", refundAmount);
        result.put("refundTotal", refundTotal);
        
        return result;
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getRevenueTotal(co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public Map<String, Long> getRevenueTotal(Site site, Date date, String period) {
        
        Map<String, Date> times = this.getTimes(date, period);
        List<RevenueReport> reports = new ArrayList<RevenueReport>();
        if(site == null) {
            reports = reportDao.listRevenueReport(times.get("startTime"), times.get("endTime"));
        } else {
            reports = reportDao.listRevenueReport(site, times.get("startTime"), times.get("endTime"));
        }
        
        long purchaseAmount = 0, purchaseTotal = 0, refundAmount = 0, refundTotal = 0;
        
        for(RevenueReport report : reports) {
            
            if(report.getRevenueType().equals(RevenueType.PURCHASE)) {
                purchaseAmount = purchaseAmount + 1;
                purchaseTotal = purchaseTotal + report.getOffer();
            } else if (report.getRevenueType().equals(RevenueType.REFUND)) {
                refundAmount = refundAmount + 1;
                refundTotal = refundTotal + report.getOffer();
            }
        }
        
        Map<String, Long> result = new HashMap<String, Long>();
        result.put("purchaseAmount", purchaseAmount);
        result.put("purchaseTotal", purchaseTotal);
        result.put("refundAmount", refundAmount);
        result.put("refundTotal", refundTotal);
        
        return result;
    }
    
    /**
     * 
     * @param statistics
     * @return
     */
    private Map<String, Long> calculateRevenueTotal(Map<String, Map<String, Map<String, RevenueReportStatistics>>> statistics) {
        long purchaseAmount = 0, purchaseTotal = 0, refundAmount = 0, refundTotal = 0;
        for(String key : statistics.keySet()) {
            for(String keySet : statistics.get(key).keySet()) {
                if(statistics.get(key).get(keySet).get("purchase") != null) {
                    purchaseAmount = purchaseAmount + statistics.get(key).get(keySet).get("purchase").getAmount();
                    purchaseTotal = purchaseTotal + statistics.get(key).get(keySet).get("purchase").getOfferTotal();
                }
                if(statistics.get(key).get(keySet).get("refund") != null) {
                    refundAmount = refundAmount + statistics.get(key).get(keySet).get("refund").getAmount();
                    refundTotal = refundTotal + statistics.get(key).get(keySet).get("refund").getOfferTotal();
                }
                
            }
        }
        
        Map<String, Long> result = new HashMap<String, Long>();
        result.put("purchaseAmount", purchaseAmount);
        result.put("purchaseTotal", purchaseTotal);
        result.put("refundAmount", refundAmount);
        result.put("refundTotal", refundTotal);
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listFinishedCourseReport(co.fitstart.entity.site.Site, java.util.Date, java.lang.String, com.demo2do.core.support.Page)
     */
    public List<FinishedCourseReport> listFinishedCourseReport(Site site, Date date, String period, Page page) {
        // 1. get startTime and endTime by date and period
        Map<String, Date> times = this.getTimes(date, period);
        
        List<FinishedCourseReport> result = new ArrayList<FinishedCourseReport>();
        if(site == null) {
            result = reportDao.listFinishedCourseReport(times.get("startTime"), times.get("endTime"), page.getBeginIndex(), page.getMaxResultRecords());
        } else {
            result = reportDao.listFinishedCourseReport(site, times.get("startTime"), times.get("endTime"), page.getBeginIndex(), page.getMaxResultRecords());
        }
        page.calculate(result.size());
        
        return result.size() > page.getEveryPage() ? result.subList(0, page.getEveryPage()) : result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listFinishedCourseReport(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    public Map<String, List<FinishedCourseReport>> listFinishedCourseReport(Site site, Date startDate,
            Date endDate) {
        List<FinishedCourseReport> reports = reportDao.listFinishedCourseReport(site, DateUtils.parseDate(DateFormatUtils.format(startDate, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(endDate, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        return CollectionUtils.constructMapValuesFromList(reports, "course.id");
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listFinishedCourseReportStatistics(co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public Map<String, Map<String, FinishedCourseReportStatistics>> listFinishedCourseReportStatistics(Site site,
            Date date, String period) {
        
        // 1. get startTime and endTime by date and period
        Map<String, Date> times = this.getTimes(date, period);
        
        List<FinishedCourseReportStatistics> result = new ArrayList<FinishedCourseReportStatistics>();
        if(site == null) {
            result = reportDao.listFinishedCourseReportStatistics(times.get("startTime"), times.get("endTime"));
        } else {
            result = reportDao.listFinishedCourseReportStatistics(site, times.get("startTime"), times.get("endTime"));
        }
        
        return this.transferFinishedCourseReport(result);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listFinishedCourseReportStatistics(java.util.List, java.util.Date, java.lang.String)
     */
    public Map<String, Map<String, FinishedCourseReportStatistics>> listFinishedCourseReportStatistics(List<Site> sites, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        List<FinishedCourseReportStatistics> result = reportDao.listFinishedCourseReportStatistics(sites, times.get("startTime"), times.get("endTime"));
        
        return this.transferFinishedCourseReport(result);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getFinishedScheduleTotal(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    public Map<String, Object> getFinishedScheduleTotal(Site site, Date startDate, Date endDate) {
        Map<String, Object> result = new HashMap<>();
        
        Date start = DateUtils.parseDate(DateFormatUtils.format(startDate, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        Date end = DateUtils.parseDate(DateFormatUtils.format(endDate, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss");
        
        if(site == null) {
            int totalAmount = reportDao.countFinishedCourseReport(start, end);
            result.put("totalAmount", totalAmount);
            
            result.put("totalOffer", totalAmount == 0 ? 0 : reportDao.sumFinishedCourseReportOffer(start, end));
        } else {
            int totalAmount = reportDao.countFinishedCourseReport(site, start, end);
            result.put("totalAmount", totalAmount);
            
            result.put("totalOffer", totalAmount == 0 ? 0 : reportDao.sumFinishedCourseReportOffer(site, start, end));
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getFinishedScheduleTotal(java.util.List, java.util.Date, java.util.Date)
     */
    public Map<String, Object> getFinishedScheduleTotal(List<Site> sites, Date startDate, Date endDate) {
        Map<String, Object> result = new HashMap<>();
        
        Date start = DateUtils.parseDate(DateFormatUtils.format(startDate, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        Date end = DateUtils.parseDate(DateFormatUtils.format(endDate, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss");
        
        int totalAmount = reportDao.countFinishedCourseReport(sites, start, end);
        result.put("totalAmount", totalAmount);
        result.put("totalOffer", totalAmount == 0 ? 0 : reportDao.sumFinishedCourseReportOffer(sites, start, end));
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listCoachFinishedScheduleReport(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    public Map<String, List<CoachFinishedScheduleReport>> listCoachFinishedScheduleReport(Site site, Date startDate, Date endDate) {
        List<CoachFinishedScheduleStat> statList;
        if(site == null) {
            statList = reportDao.listCoachFinishedScheduleStat(startDate, endDate);
        } else {
            statList = reportDao.listCoachFinishedScheduleStat(site, startDate, endDate);
        }
        
        return coachStatConvert(statList);
    }
    
    public Map<String, List<CoachFinishedScheduleReport>> listCoachFinishedScheduleReport(List<Site> sites, Date startDate, Date endDate) {
        List<CoachFinishedScheduleStat> statList = reportDao.listCoachFinishedScheduleStat(sites, startDate, endDate);
        
        return coachStatConvert(statList);
    }

    /**
     * 
     * @param statList
     * @return
     */
    private Map<String, List<CoachFinishedScheduleReport>> coachStatConvert(List<CoachFinishedScheduleStat> statList) {
        if(statList.isEmpty()) {
            return null;
        } else {
            Map<String, List<CoachFinishedScheduleReport>> reportMap = new HashMap<>();
            List<CoachFinishedScheduleReport> reportList = new ArrayList<>();
            Map<String, Long> courseMap = new HashMap<>();
            String siteName = statList.get(0).getSite();
            String coach = statList.get(0).getCoach();
            
            for(CoachFinishedScheduleStat stat : statList) {
                if(stat.getSite().equals(siteName)) {
                    if(!stat.getCoach().equals(coach)) {
                        reportList.add(new CoachFinishedScheduleReport(siteName, coach, courseMap));
                        courseMap = new HashMap<>();
                        coach = stat.getCoach();
                    }
                } else {
                    reportList.add(new CoachFinishedScheduleReport(siteName, coach, courseMap));
                    reportMap.put(siteName, reportList);
                    reportList = new ArrayList<>();
                    courseMap = new HashMap<>();
                    siteName = stat.getSite();
                    coach = stat.getCoach();
                }
                
                courseMap.put(stat.getCourse(), stat.getAmount());
            }
            
            reportList.add(new CoachFinishedScheduleReport(siteName, coach, courseMap));
            reportMap.put(siteName, reportList);
            
            return reportMap;
        }
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getSiteFinishedScheduleStat(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    public Map<Long, Long> getSiteFinishedScheduleStat(Site site, Date startDate, Date endDate) {
        Map<Long, Long> result = new HashMap<>();
        
        if(site == null) {
            List<SiteFinishedScheduleStat> siteStats = reportDao.listSiteFinishedScheduleStat(startDate, endDate);
            
            for(SiteFinishedScheduleStat stat : siteStats) {
                result.put(stat.getSiteId(), stat.getTotalAmount());
            }
        } else {
            result.put(site.getId(), (long)reportDao.countSiteFinishedSchedule(site, startDate, endDate));
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getSiteFinishedScheduleStat(java.util.List, java.util.Date, java.util.Date)
     */
    public Map<Long, Long> getSiteFinishedScheduleStat(List<Site> sites, Date startDate, Date endDate) {
        List<SiteFinishedScheduleStat> siteStats = reportDao.listSiteFinishedScheduleStat(sites, startDate, endDate);
        
        Map<Long, Long> result = new HashMap<>();
        
        for(SiteFinishedScheduleStat stat : siteStats) {
            result.put(stat.getSiteId(), stat.getTotalAmount());
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listFinishedCourseSchedule(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    public List<FinishedCourseSchedule> listFinishedCourseSchedule(Site site, Date startDate, Date endDate) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("startDate", startDate);
        parameters.put("endDate", endDate);
        
        StringBuffer sentence = new StringBuffer(
                "SELECT s.name site,csr.course_description course,"
                + "csr.course_unit course_unit, csr.course_day occur_date, csr.course_time occur_hour,"
                + "csr.sign_person sign_person, "
                + "stu.name student,p.name coach,mp.name main_coach,csr.create_time signin_time"
                + " FROM coach_signin_record csr"
                + " JOIN course_round cr ON csr.code=cr.code"
                + " JOIN site s ON csr.site_id = s.id"
                + " JOIN student stu ON cr.student_id=stu.id"
                + " JOIN principal p ON csr.principal_id = p.id"
                + " JOIN principal mp ON cr.coach_id=mp.id"
                + " WHERE csr.is_ignored = false"
                + " AND csr.course_day BETWEEN :startDate AND :endDate");
        
        if(site != null) {
            parameters.put("siteId", site.getId());
            
            sentence.append(" AND csr.site_id = :siteId");
        }
        
        sentence.append(" ORDER BY csr.site_id ASC,csr.course_description ASC,csr.create_time ASC");
        
        return genericJdbcSupport.queryForList(sentence.toString(), parameters, FinishedCourseSchedule.class);
    }

    public List<FinishedCourseSchedule> listFinishedCourseSchedule(List<Site> sites, Date startDate, Date endDate) {
        List<FinishedCourseSchedule> result = new ArrayList<>();
        
        for(Site site : sites) {
            result.addAll(this.listFinishedCourseSchedule(site, startDate, endDate));
        }
        
        return result;
    }

    /**
     * 
     * @param result
     * @return
     */
    private Map<String, Map<String, FinishedCourseReportStatistics>> transferFinishedCourseReport(List<FinishedCourseReportStatistics> result) {
        
        Map<String, List<FinishedCourseReportStatistics>> reports = CollectionUtils.constructMapValuesFromList(result, "siteId");
        Map<String, Map<String, FinishedCourseReportStatistics>> finishedCoruseReports = new HashMap<String, Map<String, FinishedCourseReportStatistics>>();
        
        for (String key : reports.keySet()) {
            finishedCoruseReports.put(key, CollectionUtils.constructMapValueFromList(reports.get(key), "courseId"));
        }
        
        return finishedCoruseReports;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getFinishedCourseTotal(java.util.Map, int)
     */
    public Map<String, Long> getFinishedCourseTotal(List<Site> sites, Map<String, Map<String, FinishedCourseReportStatistics>> statistics, int yearAmount) {
        
    	Map<String, Site> siteMap = CollectionUtils.constructMapValueFromList(sites, "id");
        
        long amount = 0, offer = 0;
        
        for(String key : statistics.keySet()) {
        	if(siteMap.containsKey(key)) {
        		for(String keySet : statistics.get(key).keySet()) {
                    if(statistics.get(key).get(keySet) != null) {
                    	amount = amount + statistics.get(key).get(keySet).getAmount();
                        offer = offer + statistics.get(key).get(keySet).getOfferTotal();
                    }
                }
        	}
        }
        
        Map<String, Long> result = new HashMap<String, Long>();
        result.put("amount", amount + yearAmount);
        result.put("offer", offer);
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listVisitorReport(co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public Map<String, VisitorReport> listVisitorReport(Site site, Date date, String period) {
        
        // 1. get startTime and endTime by date and period
        Map<String, Date> times = this.getTimes(date, period);
        
        List<VisitorReport> visitorReports = new ArrayList<VisitorReport>();
        if(site == null) {
            visitorReports = reportDao.listVisitorReport(times.get("startTime"), times.get("endTime"));
        } else {
            visitorReports = reportDao.listVisitorReport(site, times.get("startTime"), times.get("endTime"));
        }
        
        return CollectionUtils.constructMapValueFromList(visitorReports, "site.id");
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listVisitorReport(java.util.List, java.util.Date, java.lang.String)
     */
    public Map<String, VisitorReport> listVisitorReport(List<Site> sites, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        List<VisitorReport> visitorReports = reportDao.listVisitorReport(sites, times.get("startTime"), times.get("endTime"));
        
        return CollectionUtils.constructMapValueFromList(visitorReports, "site.id");
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listVisitorReport(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    public List<VisitorReport> listVisitorReport(Site site, Date startDate, Date endDate) {
        return reportDao.listVisitorReportDetail(site, DateUtils.parseDate(DateFormatUtils.format(startDate, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(endDate, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getVisitorReport(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    public VisitorReport getVisitorReport(Site site, Date startDate, Date endDate) {
        List<VisitorReport> visitorReports = reportDao.listVisitorReport(site, DateUtils.parseDate(DateFormatUtils.format(startDate, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(endDate, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        return visitorReports.size() > 0 ? visitorReports.get(0) : null;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getVisitorReportStatistics(co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public VisitorReport getVisitorReportStatistics(Map<String, VisitorReport> reports) {
        
        long visitAmount = 0, experienceAmount = 0, coachExperienceAmount = 0, orderedAmount = 0;
        
        for(String key : reports.keySet()) {
            visitAmount = visitAmount + reports.get(key).getVisitAmount();
            experienceAmount = experienceAmount + reports.get(key).getExperienceAmount();
            coachExperienceAmount = coachExperienceAmount + reports.get(key).getCoachExperienceAmount();
            orderedAmount = orderedAmount + reports.get(key).getOrderedAmount();
        }
        return new VisitorReport(visitAmount, experienceAmount, coachExperienceAmount, orderedAmount);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#editVisitAmount(co.fitstart.entity.site.Site, java.util.Date, long)
     */
    public void editVisitAmount(Site site, Date occurDate, long visitAmount) {
        VisitorReport visitorReport = reportDao.loadVisitorReport(site, occurDate);
        
        if(visitorReport == null) {
            VisitorReport report = new VisitorReport(visitAmount, 0, 0, 0, occurDate, site);
            genericDaoSupport.save(report);
        } else {
            genericDaoSupport.update(visitorReport.editVisitAmount(visitAmount));
        }
        
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listYearCourseId()
     */
    public List<Long> listYearCourseId() {
        return courseDaoSupport.getYearCourseId();
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getInbodyReport(co.fitstart.entity.site.Site, java.lang.String, co.fitstart.entity.principal.Principal, java.util.Date, java.util.Date)
     */
    public Map<String, List<InbodyRecord>> getInbodyReport(Site site, String mobile, Principal coach, Date from, Date to) {
        List<InbodyRecord> inbodyRecords = this.listInbodyRecord(site, mobile, coach, from, to);
        
        Map<String, List<InbodyRecord>> result = new HashMap<>();
        for(InbodyRecord inbodyRecord : inbodyRecords) {
            String userMobile = inbodyRecord.getMobile();
            if(inbodyRecord.getUser() != null) {
                userMobile = inbodyRecord.getUser().getMobile();
            }
            
            if(result.get(userMobile) == null) {
                result.put(userMobile, new ArrayList<InbodyRecord>());
            }
            result.get(userMobile).add(inbodyRecord);
        }
        return result;
    }

    /**
     * 
     * @param site
     * @param mobile
     * @param coach
     * @param from
     * @param to
     * @return
     */
    @SuppressWarnings("unchecked")
    private List<InbodyRecord> listInbodyRecord(Site site, String mobile, Principal coach, Date from, Date to) {
        StringBuffer sentence = new StringBuffer("FROM InbodyRecord WHERE 1 = :one");
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("one", 1);
        
        if(mobile != null) {
            User user = userDao.load(mobile);
            if(user == null) {
                sentence.append(" AND mobile = :mobile");
                parameters.put("mobile", mobile);
            } else {
                sentence.append(" AND user = :user");
                parameters.put("user", user);
            }
        }
        
        if(from != null) {
            sentence.append(" AND STR_TO_DATE(recordTime, '%Y-%m-%d %H:%i:%s') >= :from");
            parameters.put("from", DateUtils.parseDate(DateFormatUtils.format(from, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        }
        
        if(to != null) {
            sentence.append(" AND STR_TO_DATE(recordTime, '%Y-%m-%d %H:%i:%s') <= :to");
            parameters.put("to", DateUtils.parseDate(DateFormatUtils.format(to, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        }
        
        if(site != null || coach != null) {
            sentence.append(" AND user IN (SELECT DISTINCT student.user FROM CourseRound WHERE disabled = false");
            
            if(site != null) {
                sentence.append(" AND site = :site");
                parameters.put("site", site);
            }
            
            if(coach != null) {
                sentence.append(" AND coach = :coach");
                parameters.put("coach", coach);
            }
            
            sentence.append(")");
        }
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getInbodyReport(java.util.List, java.lang.String, co.fitstart.entity.principal.Principal, java.util.Date, java.util.Date)
     */
    public Map<String, List<InbodyRecord>> getInbodyReport(List<Site> sites, String mobile, Principal coach, Date from, Date to) {
        List<InbodyRecord> inbodyRecords = this.listInbodyRecord(sites, mobile, coach, from, to);
        
        Map<String, List<InbodyRecord>> result = new HashMap<>();
        for(InbodyRecord inbodyRecord : inbodyRecords) {
            String userMobile = inbodyRecord.getMobile();
            if(inbodyRecord.getUser() != null) {
                userMobile = inbodyRecord.getUser().getMobile();
            }
            
            if(result.get(userMobile) == null) {
                result.put(userMobile, new ArrayList<InbodyRecord>());
            }
            result.get(userMobile).add(inbodyRecord);
        }
        return result;
    }

    /**
     * 
     * @param sites
     * @param mobile
     * @param coach
     * @param from
     * @param to
     * @return
     */
    @SuppressWarnings("unchecked")
    private List<InbodyRecord> listInbodyRecord(List<Site> sites, String mobile, Principal coach, Date from, Date to) {
        StringBuffer sentence = new StringBuffer("FROM InbodyRecord WHERE 1 = :one");
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("one", 1);
        
        if(mobile != null) {
            User user = userDao.load(mobile);
            if(user == null) {
                sentence.append(" AND mobile = :mobile");
                parameters.put("mobile", mobile);
            } else {
                sentence.append(" AND user = :user");
                parameters.put("user", user);
            }
        }
        
        if(from != null) {
            sentence.append(" AND STR_TO_DATE(recordTime, '%Y-%m-%d %H:%i:%s') >= :from");
            parameters.put("from", DateUtils.parseDate(DateFormatUtils.format(from, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        }
        
        if(to != null) {
            sentence.append(" AND STR_TO_DATE(recordTime, '%Y-%m-%d %H:%i:%s') <= :to");
            parameters.put("to", DateUtils.parseDate(DateFormatUtils.format(to, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        }
        
        sentence.append(" AND user IN (SELECT DISTINCT student.user FROM CourseRound WHERE disabled = false AND site IN (:sites)");
        parameters.put("sites", sites);
        
        if(coach != null) {
            sentence.append(" AND coach = :coach");
            parameters.put("coach", coach);
        }
        sentence.append(")");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getInbodyReportExport(co.fitstart.entity.site.Site, java.lang.String, co.fitstart.entity.principal.Principal, java.util.Date, java.util.Date)
     */
    public Map<String, Object> getInbodyReportExport(Site site, String mobile, Principal coach, Date from, Date to) {
        Map<String, Object> result = new HashMap<>();
        
        List<InbodyRecord> inbodyRecords = this.listInbodyRecord(site, mobile, coach, from, to);
        
        Map<String, List<InbodyRecord>> inbodys = new HashMap<>();
        Set<Long> userIds = new HashSet<>();
        for(InbodyRecord inbodyRecord : inbodyRecords) {
            String userMobile = inbodyRecord.getMobile();
            if(inbodyRecord.getUser() != null) {
                userMobile = inbodyRecord.getUser().getMobile();
                userIds.add(inbodyRecord.getUser().getId());
            }
            
            if(inbodys.get(userMobile) == null) {
                inbodys.put(userMobile, new ArrayList<InbodyRecord>());
            }
            inbodys.get(userMobile).add(inbodyRecord);
        }
        result.put("inbodyRecords", inbodys);
        
        if(!userIds.isEmpty()) {
            result.put("courseRounds", this.getInbodyCourseRoundReport(userIds));
            result.put("targets", this.getInbodyStudentTargetDataReport(userIds));
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getInbodyReportExport(java.util.List, java.lang.String, co.fitstart.entity.principal.Principal, java.util.Date, java.util.Date)
     */
    public Map<String, Object> getInbodyReportExport(List<Site> sites, String mobile, Principal coach, Date from, Date to) {
        Map<String, Object> result = new HashMap<>();
        
        List<InbodyRecord> inbodyRecords = this.listInbodyRecord(sites, mobile, coach, from, to);
        
        Map<String, List<InbodyRecord>> inbodys = new HashMap<>();
        Set<Long> userIds = new HashSet<>();
        for(InbodyRecord inbodyRecord : inbodyRecords) {
            String userMobile = inbodyRecord.getMobile();
            if(inbodyRecord.getUser() != null) {
                userMobile = inbodyRecord.getUser().getMobile();
                userIds.add(inbodyRecord.getUser().getId());
            }
            
            if(inbodys.get(userMobile) == null) {
                inbodys.put(userMobile, new ArrayList<InbodyRecord>());
            }
            inbodys.get(userMobile).add(inbodyRecord);
        }
        result.put("inbodyRecords", inbodys);
        
        if(!userIds.isEmpty()) {
            result.put("courseRounds", this.getInbodyCourseRoundReport(userIds));
            result.put("targets", this.getInbodyStudentTargetDataReport(userIds));
        }
        
        return result;
    }

    /**
     * 
     * @param userIds
     * @return
     */
    private Map<String, InbodyCourseRoundReport> getInbodyCourseRoundReport(Set<Long> userIds) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseStatus", Arrays.asList(new Integer[]{RoundStatus.READY.ordinal(), RoundStatus.ACTIVATE.ordinal(), RoundStatus.EXPIRE.ordinal(), RoundStatus.FINISH.ordinal(), RoundStatus.HOLD.ordinal()}));
        parameters.put("userIds", userIds);
        
        String sentence = "SELECT cr2.mobile,p.name coach FROM course_round cr1"
                + " JOIN (SELECT s.id,s.mobile,MAX(cr.effective_start) effective_start"
                    + " FROM course_round cr JOIN student s ON cr.student_id=s.id"
                    + " WHERE cr.is_disabled=false AND cr.course_status IN (:courseStatus)"
                    + " AND s.user_id in (:userIds) GROUP BY s.id,s.mobile)cr2"
                + " ON cr1.student_id=cr2.id AND cr1.effective_start=cr2.effective_start"
                + " JOIN principal p on cr1.coach_id=p.id";
        
        return CollectionUtils.constructMapValueFromList(genericJdbcSupport.queryForList(sentence, parameters, InbodyCourseRoundReport.class), "mobile");
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getInbodyStudentTargetDataReport(java.util.Set)
     */
    private Map<String, InbodyTargetReport> getInbodyStudentTargetDataReport(Set<Long> userIds) {
        String sentence = "SELECT std2.mobile,std1.target_type,std1.weight_change,std1.bfr_change,std1.description"
                + " FROM student_target_data std1"
                + " JOIN (SELECT s.id,s.mobile,MAX(std.create_time) create_time"
                    + " FROM student_target_data std JOIN student s ON std.student_id=s.id"
                    + " WHERE std.is_disabled=false AND std.target_type IS NOT NULL AND s.user_id IN (:userIds)"
                    + " GROUP BY s.id,s.mobile)std2"
                + " ON std1.student_id=std2.id AND std1.create_time=std2.create_time";
        
        return CollectionUtils.constructMapValueFromList(genericJdbcSupport.queryForList(sentence, "userIds", userIds, InbodyTargetReport.class), "mobile");
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getBwhReport(co.fitstart.entity.site.Site, java.lang.String, co.fitstart.entity.principal.Principal, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public Map<String, List<BwhRecord>> getBwhReport(Site site, String mobile, Principal coach, Date from, Date to) {
        StringBuffer sentence = new StringBuffer("FROM BwhRecord WHERE 1 = :one");
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("one", 1);
        
        if(site != null) {
            sentence.append(" AND courseRound.site = :site");
            parameters.put("site", site);
        }
        
        if(mobile != null) {
            sentence.append(" AND user.mobile = :mobile");
            parameters.put("mobile", mobile);
        }
        
        if(coach != null) {
            sentence.append(" AND courseRound.coach = :coach");
            parameters.put("coach", coach);
        }
        
        if(from != null) {
            sentence.append(" AND createTime >= :from");
            parameters.put("from", from);
        }
        
        if(to != null) {
            sentence.append(" AND createTime < :to");
            parameters.put("to", to);
        }
        
        List<BwhRecord> bwhRecords = genericDaoSupport.searchForList(sentence.toString(), parameters);
        
        return CollectionUtils.constructMapValuesFromList(bwhRecords, "user.mobile");
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getBwhReport(java.util.List, java.lang.String, co.fitstart.entity.principal.Principal, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public Map<String, List<BwhRecord>> getBwhReport(List<Site> sites, String mobile, Principal coach, Date from, Date to) {
        StringBuffer sentence = new StringBuffer("FROM BwhRecord WHERE 1 = :one");
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("one", 1);
        
        sentence.append(" AND courseRound.site IN (:sites)");
        parameters.put("sites", sites);
        
        if(mobile != null) {
            sentence.append(" AND user.mobile = :mobile");
            parameters.put("mobile", mobile);
        }
        
        if(coach != null) {
            sentence.append(" AND courseRound.coach = :coach");
            parameters.put("coach", coach);
        }
        
        if(from != null) {
            sentence.append(" AND createTime >= :from");
            parameters.put("from", from);
        }
        
        if(to != null) {
            sentence.append(" AND createTime < :to");
            parameters.put("to", to);
        }
        
        List<BwhRecord> bwhRecords = genericDaoSupport.searchForList(sentence.toString(), parameters);
        
        return CollectionUtils.constructMapValuesFromList(bwhRecords, "user.mobile");
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listTasteStatQuestion(java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<TasteStatSingle> listTasteStatQuestion(Date day) {
        Map<String, Date> times = this.getTimes(day, "day");
        
        String sentence = "SELECT new co.fitstart.admin.entity.report.TasteStatSingle(DATE_FORMAT(createTime,'%k'), COUNT(id)) FROM BMIQuestion"
                + " WHERE createTime >= :begin AND createTime <= :end GROUP BY DATE_FORMAT(createTime,'%k')";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("begin", times.get("startTime"));
        parameters.put("end", times.get("endTime"));
        
        return genericDaoSupport.searchForList(sentence, parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listMonthTasteStatQuestion(java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<TasteStatSingle> listMonthTasteStatQuestion(Date day) {
        Map<String, Date> times = this.getTimes(day, "month");
        
        String sentence = "SELECT new co.fitstart.admin.entity.report.TasteStatSingle(DATE_FORMAT(createTime,'%e'), COUNT(id)) FROM BMIQuestion"
                + " WHERE createTime >= :begin AND createTime <= :end GROUP BY DATE_FORMAT(createTime,'%e')";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("begin", times.get("startTime"));
        parameters.put("end", times.get("endTime"));
        
        return genericDaoSupport.searchForList(sentence, parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listTasteStatProfile(java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<TasteStatSingle> listTasteStatProfile(Date day) {
        Map<String, Date> times = this.getTimes(day, "day");
        
        String sentence = "SELECT new co.fitstart.admin.entity.report.TasteStatSingle(DATE_FORMAT(createTime,'%k'), COUNT(id)) FROM TasteSignUp"
                + " WHERE createTime >= :begin AND createTime <= :end GROUP BY DATE_FORMAT(createTime,'%k')";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("begin", times.get("startTime"));
        parameters.put("end", times.get("endTime"));
        
        return genericDaoSupport.searchForList(sentence, parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listMonthTasteStatProfile(java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<TasteStatSingle> listMonthTasteStatProfile(Date day) {
        Map<String, Date> times = this.getTimes(day, "month");
        
        String sentence = "SELECT new co.fitstart.admin.entity.report.TasteStatSingle(DATE_FORMAT(createTime,'%e'), COUNT(id)) FROM TasteSignUp"
                + " WHERE createTime >= :begin AND createTime <= :end GROUP BY DATE_FORMAT(createTime,'%e')";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("begin", times.get("startTime"));
        parameters.put("end", times.get("endTime"));
        
        return genericDaoSupport.searchForList(sentence, parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listTasteStatOrder(java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<TasteStatSingle> listTasteStatOrder(Date day) {
        Map<String, Date> times = this.getTimes(day, "day");
        
        String sentence = "SELECT new co.fitstart.admin.entity.report.TasteStatSingle(DATE_FORMAT(taste.order.payTime,'%k'), COUNT(taste.order.id)) FROM TasteSignUp taste"
                + " WHERE taste.order.paymentStatus = :paid AND taste.order.payTime >= :begin AND taste.order.payTime <= :end GROUP BY DATE_FORMAT(taste.order.payTime,'%k')";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("begin", times.get("startTime"));
        parameters.put("end", times.get("endTime"));
        
        return genericDaoSupport.searchForList(sentence, parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listMonthTasteStatOrder(java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<TasteStatSingle> listMonthTasteStatOrder(Date day) {
        Map<String, Date> times = this.getTimes(day, "month");
        
        String sentence = "SELECT new co.fitstart.admin.entity.report.TasteStatSingle(DATE_FORMAT(taste.order.payTime,'%e'), COUNT(taste.order.id)) FROM TasteSignUp taste"
                + " WHERE taste.order.paymentStatus = :paid AND taste.order.payTime >= :begin AND taste.order.payTime <= :end GROUP BY DATE_FORMAT(taste.order.payTime,'%e')";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("begin", times.get("startTime"));
        parameters.put("end", times.get("endTime"));
        
        return genericDaoSupport.searchForList(sentence, parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listTasteStat(java.lang.String, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<TasteStat> listTasteStat(String source, Date day) {
        String sentence = "FROM TasteStat WHERE statDay = :day AND source = :source";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("day", day);
        parameters.put("source", source);
        
        return genericDaoSupport.searchForList(sentence, parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listTasteStatEveryDay(java.lang.String, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<TasteStatEveryDay> listTasteStatEveryDay(String source, Date day) {
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.TasteStatEveryDay(DATE_FORMAT(statDay, '%Y-%m-%d'), SUM(questionNum), SUM(profileNum), SUM(orderNum)) FROM TasteStat"
                + " WHERE statDay >= :firstDay AND statDay <= :lastDay");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("firstDay", DateUtils.getFirstMonthDay(day));
        parameters.put("lastDay", DateUtils.getLastMonthDay(day));
        
        if(source != null) {
            sentence.append(" AND source = :source");
            parameters.put("source", source);
        }
        
        sentence.append(" GROUP BY statDay");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listTasteSignUp(java.lang.String, java.util.Date)
     */
    public List<TasteSignUp> listTasteSignUp(String source, Date day) {
        Map<String, Date> times = this.getTimes(day, "day");
        
        return listTasteSignUp(source, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listTasteSignUp(java.lang.String, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<TasteSignUp> listTasteSignUp(String source, Date begin, Date end) {
        StringBuffer sentence = new StringBuffer("FROM TasteSignUp WHERE createTime >= :begin AND createTime <= :end");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        if(source != null) {
            sentence.append(" AND source = :source");
            parameters.put("source", source);
        }
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listTasteStatSource()
     */
    public List<String> listTasteStatSource() {
        return genericJdbcSupport.queryForSingleColumnList("SELECT DISTINCT source FROM bmi_question"
                + " WHERE source IS NOT NULL AND 1 = :one"
                + " UNION"
                + " SELECT DISTINCT source FROM taste_sign_up"
                + " WHERE source IS NOT NULL"
                + " UNION"
                + " SELECT DISTINCT source FROM taste_stat"
                + " WHERE source is NOT NULL", "one", 1, String.class);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#addService(co.fitstart.entity.taste.TasteSignUp)
     */
    public void addService(TasteSignUp tasteSignUp) {
        genericDaoSupport.update(tasteSignUp.addService());
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countBMIQuestion(java.lang.String, java.util.Date, java.lang.String)
     */
    public int countBMIQuestion(String source, Date day, String period) {
        Map<String, Date> times = this.getTimes(day, period);
        
        return countBMIQuestion(source, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countBMIQuestion(java.lang.String, java.util.Date, java.util.Date)
     */
    public int countBMIQuestion(String source, Date begin, Date end) {
        StringBuffer sentence = new StringBuffer("FROM BMIQuestion WHERE createTime >= :begin AND createTime <= :end");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        if(source != null) {
            sentence.append(" AND source = :source");
            parameters.put("source", source);
        }
        
        return genericDaoSupport.count(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countTasteSignUp(java.lang.String, java.util.Date, java.lang.String)
     */
    public int countTasteSignUp(String source, Date day, String period) {
        Map<String, Date> times = this.getTimes(day, period);
        
        return countTasteSignUp(source, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countTasteSignUp(java.lang.String, java.util.Date, java.util.Date)
     */
    public int countTasteSignUp(String source, Date begin, Date end) {
        StringBuffer sentence = new StringBuffer("FROM TasteSignUp WHERE createTime >= :begin AND createTime <= :end");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        if(source != null) {
            sentence.append(" AND source = :source");
            parameters.put("source", source);
        }
        
        return genericDaoSupport.count(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countOrder(java.lang.String, java.util.Date, java.lang.String)
     */
    public int countOrder(String source, Date day, String period) {
        Map<String, Date> times = this.getTimes(day, period);
        
        return countOrder(source, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countOrder(java.lang.String, java.util.Date, java.util.Date)
     */
    public int countOrder(String source, Date begin, Date end) {
        StringBuffer sentence = new StringBuffer("FROM TasteSignUp WHERE order.paymentStatus = :paid AND order.payTime >= :begin AND order.payTime <= :end");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        if(source != null) {
            sentence.append(" AND source = :source");
            parameters.put("source", source);
        }
        
        return genericDaoSupport.count(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countFinishedCourse(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    public int countFinishedCourse(Date begin, Date end, Site site) {
        StringBuffer sentence = new StringBuffer("SELECT COUNT(*) FROM CourseRound"
                + " WHERE disabled=false AND courseStatus= :finish"
                + " AND finishTime >= :begin AND finishTime <= :end");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("finish", RoundStatus.FINISH);
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        if(site != null) {
            parameters.put("site", site);
            sentence.append(" AND site = :site");
        }
        
        return genericDaoSupport.count(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countFinishedCourse(java.util.Date, java.util.Date, java.util.List)
     */
    public int countFinishedCourse(Date begin, Date end, List<Site> sites) {
        StringBuffer sentence = new StringBuffer("SELECT COUNT(*) FROM CourseRound"
                + " WHERE disabled=false AND courseStatus= :finish"
                + " AND finishTime >= :begin AND finishTime <= :end");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("finish", RoundStatus.FINISH);
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        parameters.put("sites", sites);
        sentence.append(" AND site IN (:sites)");
        
        return genericDaoSupport.count(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listOfflineCourse()
     */
    @SuppressWarnings("unchecked")
    public List<Course> listOfflineCourse() {
        String sentence = "FROM Course WHERE online=false AND group=false ORDER BY hours ASC,id DESC";
        return genericDaoSupport.searchForList(sentence);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listFinishedCourseCount(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    @SuppressWarnings("unchecked")
    public Map<Long, Long> listFinishedCourseCount(Date begin, Date end, Site site) {
        String sentence = "SELECT new co.fitstart.admin.entity.report.FinishedCourseCount(course, count(*)) FROM CourseRound round"
                + " WHERE round.disabled=false AND round.courseStatus= :finish"
                + " AND round.finishTime >= :begin AND round.finishTime <= :end AND round.site = :site"
                + " GROUP BY round.course";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("finish", RoundStatus.FINISH);
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("site", site);
        
        List<FinishedCourseCount> finishedCourseCountList = genericDaoSupport.searchForList(sentence, parameters);
        
        Map<Long, Long> result  = new HashMap<>();
        for(FinishedCourseCount finishedCourseCount : finishedCourseCountList) {
            result.put(finishedCourseCount.getCourse().getId(), finishedCourseCount.getNum());
        }
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listFinishedCourseRound(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    @SuppressWarnings("unchecked")
    public List<CourseRound> listFinishedCourseRound(Date begin, Date end, Site site) {
        StringBuffer sentence = new StringBuffer("FROM CourseRound"
                + " WHERE disabled=false AND courseStatus= :finish"
                + " AND finishTime >= :begin AND finishTime <= :end");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("finish", RoundStatus.FINISH);
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        if(site != null) {
            sentence.append(" AND site = :site");
            parameters.put("site", site);
        }
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listFinishedCourseRound(java.util.Date, java.util.Date, java.util.List)
     */
    @SuppressWarnings("unchecked")
    public List<CourseRound> listFinishedCourseRound(Date begin, Date end, List<Site> sites) {
        StringBuffer sentence = new StringBuffer("FROM CourseRound"
                + " WHERE disabled=false AND courseStatus= :finish"
                + " AND finishTime >= :begin AND finishTime <= :end");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("finish", RoundStatus.FINISH);
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        sentence.append(" AND site IN (:sites)");
        parameters.put("sites", sites);
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listStudentUnitPrice(co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public List<StudentUnitPrice> listStudentUnitPrice(Site site, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listStudentUnitPrice(site, times.get("startTime"), times.get("endTime"));
    }
    
    public List<StudentUnitPrice> listStudentUnitPrice(List<Site> sites, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listStudentUnitPrice(sites, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getStudentUnitPrice(java.util.Date, java.lang.String)
     */
    public StudentUnitPrice getStudentUnitPrice(Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return getStudentUnitPrice(times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getStudentUnitPrice(java.util.List, java.util.Date, java.lang.String)
     */
    public StudentUnitPrice getStudentUnitPrice(List<Site> sites, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return getStudentUnitPrice(sites, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listStudentCourseRoundStat(co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public List<StudentCourseRoundStat> listStudentCourseRoundStat(Site site, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listStudentCourseRoundStat(site, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listStudentCourseRoundStat(java.util.List, java.util.Date, java.lang.String)
     */
    public List<StudentCourseRoundStat> listStudentCourseRoundStat(List<Site> sites, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listStudentCourseRoundStat(sites, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listCourseRound(co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public List<CourseRound> listCourseRound(Site site, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listCourseRound(site, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listCourseRound(java.util.List, java.util.Date, java.lang.String)
     */
    public List<CourseRound> listCourseRound(List<Site> sites, Date date, String period){
        Map<String, Date> times = this.getTimes(date, period);
        
        return listCourseRound(sites, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listStudentUnitPrice(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<StudentUnitPrice> listStudentUnitPrice(Site site, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.StudentUnitPrice(site.name,SUM(price),COUNT(DISTINCT student)) FROM Order"
                + " WHERE disabled=false AND orderStatus IN (:orderStatus)"
                + " AND paymentStatus = :paid AND payTime >= :startTime"
                + " AND payTime <= :endTime AND orderType IN (:orderType)"
                + " AND course.experience = false");
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("orderType", new OrderType[]{OrderType.INDIVIDUAL, OrderType.YEAR});
        
        if(site != null) {
            sentence.append(" AND site = :site");
            parameters.put("site", site);
        }
        
        sentence.append(" GROUP BY site");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listStudentUnitPrice(java.util.List, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<StudentUnitPrice> listStudentUnitPrice(List<Site> sites, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.StudentUnitPrice(site.name,SUM(price),COUNT(DISTINCT student)) FROM Order"
                + " WHERE disabled=false AND orderStatus IN (:orderStatus)"
                + " AND paymentStatus = :paid AND payTime >= :startTime"
                + " AND payTime <= :endTime AND orderType IN (:orderType)"
                + " AND course.experience = false");
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("orderType", new OrderType[]{OrderType.INDIVIDUAL, OrderType.YEAR});
        
        sentence.append(" AND site IN (:sites)");
        parameters.put("sites", sites);
        
        sentence.append(" GROUP BY site");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getStudentUnitPrice(java.util.Date, java.util.Date)
     */
    public StudentUnitPrice getStudentUnitPrice(Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        String sentence = "SELECT new co.fitstart.admin.entity.report.StudentUnitPrice(sum(price),count(DISTINCT student)) FROM Order"
                + " WHERE disabled=false AND orderStatus IN (:orderStatus)"
                + " AND paymentStatus = :paid AND payTime >= :startTime"
                + " AND payTime <= :endTime AND orderType IN (:orderType)"
                + " AND course.experience = false";
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("orderType", new OrderType[]{OrderType.INDIVIDUAL, OrderType.YEAR});
        
        return genericDaoSupport.searchForObject(sentence, parameters, StudentUnitPrice.class);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getStudentUnitPrice(java.util.List, java.util.Date, java.util.Date)
     */
    public StudentUnitPrice getStudentUnitPrice(List<Site> sites, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        String sentence = "SELECT new co.fitstart.admin.entity.report.StudentUnitPrice(sum(price),count(DISTINCT student)) FROM Order"
                + " WHERE disabled=false AND orderStatus IN (:orderStatus)"
                + " AND paymentStatus = :paid AND payTime >= :startTime"
                + " AND payTime <= :endTime AND orderType IN (:orderType)"
                + " AND course.experience = false AND site IN (:sites)";
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("orderType", new OrderType[]{OrderType.INDIVIDUAL, OrderType.YEAR});
        parameters.put("sites", sites);
        
        return genericDaoSupport.searchForObject(sentence, parameters, StudentUnitPrice.class);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listCourseRound(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<CourseRound> listCourseRound(Site site, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("FROM CourseRound"
                + " WHERE disabled=false AND order.orderStatus IN (:orderStatus)"
                + " AND order.paymentStatus = :paid AND order.payTime >= :startTime"
                + " AND order.payTime <= :endTime AND courseType IN (:courseType)"
                + " AND course.experience = false");
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("courseType", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR});
        
        if(site != null) {
            sentence.append(" AND site = :site");
            parameters.put("site", site);
        }
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listCourseRound(java.util.List, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<CourseRound> listCourseRound(List<Site> sites, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("FROM CourseRound"
                + " WHERE disabled=false AND order.orderStatus IN (:orderStatus)"
                + " AND order.paymentStatus = :paid AND order.payTime >= :startTime"
                + " AND order.payTime <= :endTime AND courseType IN (:courseType)"
                + " AND course.experience = false");
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("courseType", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR});
        
        sentence.append(" AND site IN (:sites)");
        parameters.put("sites", sites);
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listStudentCourseRoundStat(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<StudentCourseRoundStat> listStudentCourseRoundStat(Site site, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.StudentCourseRoundStat(site.name,SUM(c.order.price),student.name,student.mobile,count(id)) FROM CourseRound c"
                + " WHERE disabled=false AND order.orderStatus IN (:orderStatus)"
                + " AND order.paymentStatus = :paid AND order.payTime >= :startTime"
                + " AND order.payTime <= :endTime AND courseType IN (:courseType)"
                + " AND course.experience = false");
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("courseType", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR});
        
        if(site != null) {
            sentence.append(" AND site = :site");
            parameters.put("site", site);
        }
        
        sentence.append(" GROUP BY site,student");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listStudentCourseRoundStat(java.util.List, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<StudentCourseRoundStat> listStudentCourseRoundStat(List<Site> sites, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.StudentCourseRoundStat(site.name,SUM(c.order.price),student.name,student.mobile,count(id)) FROM CourseRound c"
                + " WHERE disabled=false AND order.orderStatus IN (:orderStatus)"
                + " AND order.paymentStatus = :paid AND order.payTime >= :startTime"
                + " AND order.payTime <= :endTime AND courseType IN (:courseType)"
                + " AND course.experience = false");
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("courseType", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR});
        
        sentence.append(" AND site IN (:sites)");
        parameters.put("sites", sites);
        
        sentence.append(" GROUP BY site,student");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listHourUnitPrice(co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public List<HourUnitPrice> listHourUnitPrice(Site site, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listHourUnitPrice(site, times.get("startTime"), times.get("endTime"));
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listHourUnitPrice(java.util.List, java.util.Date, java.lang.String)
     */
    public List<HourUnitPrice> listHourUnitPrice(List<Site> sites, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listHourUnitPrice(sites, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getHourUnitPrice(java.util.Date, java.lang.String)
     */
    public HourUnitPrice getHourUnitPrice(Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return getHourUnitPrice(times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getHourUnitPrice(java.util.List, java.util.Date, java.lang.String)
     */
    public HourUnitPrice getHourUnitPrice(List<Site> sites, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return getHourUnitPrice(sites, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listHourUnitPrice(co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<HourUnitPrice> listHourUnitPrice(Site site, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.HourUnitPrice(courseRound.site.name,SUM(courseRound.order.price),SUM(case when value='1000' then 104 else (cast(value as integer)) end)) FROM CourseRoundAttribute"
                + " WHERE  name='course_amount' AND courseRound.order.disabled=false AND courseRound.order.orderStatus IN (:orderStatus)"
                + " AND courseRound.order.paymentStatus = :paid AND courseRound.order.payTime >= :startTime"
                + " AND courseRound.order.payTime <= :endTime AND courseRound.courseType IN (:courseType)"
                + " AND courseRound.course.experience = false");
        /*StringBuffer sentence = new StringBuffer("SELECT courseRound.site.name siteName,SUM(courseRound.order.price) totalAmount,SUM(IF(value='1000',104,(cast value as integer)))) FROM CourseRoundAttribute"
                + " WHERE  name='course_amount' AND courseRound.order.disabled=false AND courseRound.order.orderStatus IN (:orderStatus)"
                + " AND courseRound.order.paymentStatus = :paid AND courseRound.order.payTime >= :startTime"
                + " AND courseRound.order.payTime <= :endTime AND courseRound.order.orderType IN (:orderType)"
                + " AND courseRound.course.experience = false");*/
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("courseType", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR});
        
        if(site != null) {
            sentence.append(" AND courseRound.site = :site");
            parameters.put("site", site);
        }
        
        sentence.append(" GROUP BY courseRound.site");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listHourUnitPrice(java.util.List, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<HourUnitPrice> listHourUnitPrice(List<Site> sites, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.HourUnitPrice(courseRound.site.name,SUM(courseRound.order.price),SUM(case when value='1000' then 104 else (cast(value as integer)) end)) FROM CourseRoundAttribute"
                + " WHERE  name='course_amount' AND courseRound.order.disabled=false AND courseRound.order.orderStatus IN (:orderStatus)"
                + " AND courseRound.order.paymentStatus = :paid AND courseRound.order.payTime >= :startTime"
                + " AND courseRound.order.payTime <= :endTime AND courseRound.courseType IN (:courseType)"
                + " AND courseRound.course.experience = false");
        /*StringBuffer sentence = new StringBuffer("SELECT courseRound.site.name siteName,SUM(courseRound.order.price) totalAmount,SUM(IF(value='1000',104,(cast value as integer)))) FROM CourseRoundAttribute"
                + " WHERE  name='course_amount' AND courseRound.order.disabled=false AND courseRound.order.orderStatus IN (:orderStatus)"
                + " AND courseRound.order.paymentStatus = :paid AND courseRound.order.payTime >= :startTime"
                + " AND courseRound.order.payTime <= :endTime AND courseRound.order.orderType IN (:orderType)"
                + " AND courseRound.course.experience = false");*/
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("courseType", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR});
        
        sentence.append(" AND courseRound.site IN (:sites)");
        parameters.put("sites", sites);
        
        sentence.append(" GROUP BY courseRound.site");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getHourUnitPrice(java.util.Date, java.util.Date)
     */
    public HourUnitPrice getHourUnitPrice(Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        String sentence = "SELECT new co.fitstart.admin.entity.report.HourUnitPrice(sum(courseRound.order.price),SUM(case when value='1000' then 104 else (cast(value as integer)) end)) FROM CourseRoundAttribute"
                + " WHERE name='course_amount' AND courseRound.order.disabled=false AND courseRound.order.orderStatus IN (:orderStatus)"
                + " AND courseRound.order.paymentStatus = :paid AND courseRound.order.payTime >= :startTime"
                + " AND courseRound.order.payTime <= :endTime AND courseRound.courseType IN (:courseType)"
                + " AND courseRound.course.experience = false";
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("courseType", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR});
        
        return genericDaoSupport.searchForObject(sentence, parameters, HourUnitPrice.class);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getHourUnitPrice(java.util.List, java.util.Date, java.util.Date)
     */
    public HourUnitPrice getHourUnitPrice(List<Site> sites, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        String sentence = "SELECT new co.fitstart.admin.entity.report.HourUnitPrice(sum(courseRound.order.price),SUM(case when value='1000' then 104 else (cast(value as integer)) end)) FROM CourseRoundAttribute"
                + " WHERE name='course_amount' AND courseRound.order.disabled=false AND courseRound.order.orderStatus IN (:orderStatus)"
                + " AND courseRound.order.paymentStatus = :paid AND courseRound.order.payTime >= :startTime"
                + " AND courseRound.order.payTime <= :endTime AND courseRound.courseType IN (:courseType)"
                + " AND courseRound.course.experience = false AND courseRound.site IN (:sites)";
        parameters.put("orderStatus", new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD});
        parameters.put("paid", PaymentStatus.PAID);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("courseType", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR});
        parameters.put("sites", sites);
        
        return genericDaoSupport.searchForObject(sentence, parameters, HourUnitPrice.class);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listSiteHour(co.fitstart.entity.course.CourseType, co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public List<SiteHour> listSiteHour(CourseType courseType, Site site, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listSiteHour(courseType, site, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listSiteHour(co.fitstart.entity.course.CourseType, java.util.List, java.util.Date, java.lang.String)
     */
    public List<SiteHour> listSiteHour(CourseType courseType, List<Site> sites, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listSiteHour(courseType, sites, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getSiteHour(co.fitstart.entity.course.CourseType, java.util.Date, java.lang.String)
     */
    public int getSiteHour(CourseType courseType, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return getSiteHour(courseType, times.get("startTime"), times.get("endTime"));
    }
    
    public int getSiteHour(CourseType courseType, List<Site> sites, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return getSiteHour(courseType, sites, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listSiteHourStat(co.fitstart.entity.course.CourseType, co.fitstart.entity.site.Site, java.util.Date, java.lang.String)
     */
    public List<SiteHourStat> listSiteHourStat(CourseType courseType, Site site, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listSiteHourStat(courseType, site, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listSiteHourStat(co.fitstart.entity.course.CourseType, java.util.List, java.util.Date, java.lang.String)
     */
    public List<SiteHourStat> listSiteHourStat(CourseType courseType, List<Site> sites, Date date, String period) {
        Map<String, Date> times = this.getTimes(date, period);
        
        return listSiteHourStat(courseType, sites, times.get("startTime"), times.get("endTime"));
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listSiteHour(co.fitstart.entity.course.CourseType, java.util.Date, java.lang.String)
     */
    @SuppressWarnings("unchecked")
    public List<SiteHour> listSiteHour(CourseType courseType, Site site, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.SiteHour(site.name,COUNT(id)) FROM CourseSchedule"
                + " WHERE disabled=false"
                + " AND courseType= :courseType AND course.experience = false"
                + " AND scheduleStatus= :finish"
                + " AND occurDate >= :startTime AND occurDate <= :endTime");
        parameters.put("courseType", courseType);
        parameters.put("finish", ScheduleStatus.FINISH);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        if(site != null) {
            sentence.append(" AND site= :site");
            parameters.put("site", site);
        }
        
        sentence.append(" GROUP BY site");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listSiteHour(co.fitstart.entity.course.CourseType, java.util.List, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<SiteHour> listSiteHour(CourseType courseType, List<Site> sites, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.SiteHour(site.name,COUNT(id)) FROM CourseSchedule"
                + " WHERE disabled=false"
                + " AND courseType= :courseType AND course.experience = false"
                + " AND scheduleStatus= :finish"
                + " AND occurDate >= :startTime AND occurDate <= :endTime");
        parameters.put("courseType", courseType);
        parameters.put("finish", ScheduleStatus.FINISH);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        sentence.append(" AND site IN (:sites)");
        parameters.put("sites", sites);
        
        sentence.append(" GROUP BY site");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getSiteHour(co.fitstart.entity.course.CourseType, java.util.Date, java.util.Date)
     */
    public int getSiteHour(CourseType courseType, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        String sentence = "FROM CourseSchedule"
                + " WHERE disabled=false"
                + " AND courseType= :courseType AND course.experience = false"
                + " AND scheduleStatus= :finish"
                + " AND occurDate >= :startTime AND occurDate <= :endTime";
        parameters.put("courseType", courseType);
        parameters.put("finish", ScheduleStatus.FINISH);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return genericDaoSupport.count(sentence, parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#getSiteHour(co.fitstart.entity.course.CourseType, java.util.List, java.util.Date, java.util.Date)
     */
    public int getSiteHour(CourseType courseType, List<Site> sites, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        String sentence = "FROM CourseSchedule"
                + " WHERE disabled=false"
                + " AND courseType= :courseType AND course.experience = false"
                + " AND site IN (:sites)"
                + " AND scheduleStatus= :finish"
                + " AND occurDate >= :startTime AND occurDate <= :endTime";
        parameters.put("courseType", courseType);
        parameters.put("sites", sites);
        parameters.put("finish", ScheduleStatus.FINISH);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return genericDaoSupport.count(sentence, parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listSiteHourStat(co.fitstart.entity.course.CourseType, co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<SiteHourStat> listSiteHourStat(CourseType courseType, Site site, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.SiteHourStat(site.name,course.name,student.name,student.mobile,courseRound.coach.name,coach.name,COUNT(id),courseRound.effectiveStart,courseRound.effectiveEnd) FROM CourseSchedule"
                + " WHERE disabled=false"
                + " AND courseType= :courseType AND course.experience = false"
                + " AND scheduleStatus= :finish"
                + " AND occurDate >= :startTime AND occurDate <= :endTime");
        parameters.put("courseType", courseType);
        parameters.put("finish", ScheduleStatus.FINISH);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        if(site != null) {
            sentence.append(" AND site= :site");
            parameters.put("site", site);
        }
        
        sentence.append(" GROUP BY site,courseRound,coach");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listSiteHourStat(co.fitstart.entity.course.CourseType, java.util.List, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<SiteHourStat> listSiteHourStat(CourseType courseType, List<Site> sites, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.SiteHourStat(site.name,course.name,student.name,student.mobile,courseRound.coach.name,coach.name,COUNT(id),courseRound.effectiveStart,courseRound.effectiveEnd) FROM CourseSchedule"
                + " WHERE disabled=false"
                + " AND courseType= :courseType AND course.experience = false"
                + " AND scheduleStatus= :finish"
                + " AND occurDate >= :startTime AND occurDate <= :endTime");
        parameters.put("courseType", courseType);
        parameters.put("finish", ScheduleStatus.FINISH);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        sentence.append(" AND site IN (:sites)");
        parameters.put("sites", sites);
        
        sentence.append(" GROUP BY site,courseRound,coach");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listCourseSchedule(co.fitstart.entity.course.CourseType, co.fitstart.entity.site.Site, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<CourseSchedule> listCourseSchedule(CourseType courseType, Site site, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("FROM CourseSchedule"
                + " WHERE disabled=false"
                + " AND courseType= :courseType AND course.experience = false"
                + " AND scheduleStatus= :finish"
                + " AND occurDate >= :startTime AND occurDate <= :endTime");
        parameters.put("courseType", courseType);
        parameters.put("finish", ScheduleStatus.FINISH);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        if(site != null) {
            sentence.append(" AND site = :site");
            parameters.put("site", site);
        }
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listCourseSchedule(co.fitstart.entity.course.CourseType, java.util.List, java.util.Date, java.util.Date)
     */
    @SuppressWarnings("unchecked")
    public List<CourseSchedule> listCourseSchedule(CourseType courseType, List<Site> sites, Date begin, Date end) {
        Map<String, Object> parameters = new HashMap<String, Object>();
        
        StringBuffer sentence = new StringBuffer("FROM CourseSchedule"
                + " WHERE disabled=false"
                + " AND courseType= :courseType AND course.experience = false"
                + " AND scheduleStatus= :finish"
                + " AND occurDate >= :startTime AND occurDate <= :endTime");
        parameters.put("courseType", courseType);
        parameters.put("finish", ScheduleStatus.FINISH);
        parameters.put("startTime", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endTime", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        sentence.append(" AND site IN (:sites)");
        parameters.put("sites", sites);
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#search(co.fitstart.admin.entity.search.SigninReportSearch)
     */
    @SuppressWarnings("unchecked")
    public List<CourseRound> search(SigninReportSearch search) {
        StringBuffer sentence = new StringBuffer("SELECT cr.id FROM course_round cr"
                + " JOIN course c ON cr.course_id=c.id");
        
        if(StringUtils.isNotBlank(search.getName()) || StringUtils.isNotBlank(search.getMobile())) {
            sentence.append(" JOIN student s ON cr.student_id=s.id");
        }
        
        sentence.append(" LEFT JOIN course_schedule cs"
                + " ON cr.id=cs.course_round_id AND cs.is_disabled = false"
                + " AND cs.schedule_status = :finish AND cs.occur_date BETWEEN :begin and :end"
                + " WHERE cr.is_disabled = false AND cr.course_type in (:individual,:year)"
                + " AND c.is_experience=false AND cr.course_status in (:ready,:hold,:expire,:activate) AND cs.id is null");
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("finish", ScheduleStatus.FINISH.ordinal());
        parameters.put("begin", search.getBegin());
        parameters.put("end", search.getEnd());
        parameters.put("individual", CourseType.INDIVIDUAL.ordinal());
        parameters.put("year", CourseType.YEAR.ordinal());
        parameters.put("ready", RoundStatus.READY.ordinal());
        parameters.put("hold", RoundStatus.HOLD.ordinal());
        parameters.put("expire", RoundStatus.EXPIRE.ordinal());
        parameters.put("activate", RoundStatus.ACTIVATE.ordinal());
        
        if(search.getSite() != null) {
            sentence.append(" AND cr.site_id = :siteId");
            
            parameters.put("siteId", search.getSite().getId());
        }
        
        if(search.getSites() != null && !search.getSites().isEmpty()) {
            List<Long> siteIds = new ArrayList<>();
            for(Site site : search.getSites()) {
                siteIds.add(site.getId());
            }
            sentence.append(" AND cr.site_id IN (:siteIds)");
            
            parameters.put("siteIds", siteIds);
        }
        
        if(search.getCoach() != null) {
            sentence.append(" AND cr.coach_id = :coachId");
            
            parameters.put("coachId", search.getCoach().getId());
        }
        
        if(StringUtils.isNotBlank(search.getName())) {
            sentence.append(" AND s.name LIKE :studentName");
            
            parameters.put("studentName", "%" + search.getName() + "%");
        }
        
        if(StringUtils.isNotBlank(search.getMobile())) {
            sentence.append(" AND s.mobile LIKE :studentMobile");
            
            parameters.put("studentMobile", "%" + search.getMobile() + "%");
        }
        
        List<Long> courseRoundIds = genericJdbcSupport.queryForSingleColumnList(sentence.toString(), parameters, Long.class);
        
        if(courseRoundIds.isEmpty()) {
            return null;
        } else {
            return genericDaoSupport.searchForList("FROM CourseRound WHERE id IN (:courseRoundIds) ORDER BY site,coach", "courseRoundIds", courseRoundIds);
        }
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listUnfinishedRoundStat(co.fitstart.entity.site.Site, co.fitstart.entity.principal.Principal)
     */
    public List<UnfinishedRoundReport> listUnfinishedRoundStat(Site site, Principal coach) {
        List<UnfinishedRoundStat> statByTypeAndStatusList = statByTypeAndStatus(site, coach);
        
        Map<String, Map<String, Long>> statMap = stat(site, coach);
        
        List<UnfinishedRoundReport> reportList = new ArrayList<>();
        
        if(!statByTypeAndStatusList.isEmpty()) {
            UnfinishedRoundReport report = new UnfinishedRoundReport(statByTypeAndStatusList.get(0).getSite(), statByTypeAndStatusList.get(0).getCoach());
            for(UnfinishedRoundStat stat : statByTypeAndStatusList) {
                if(!stat.getSite().equals(report.getSite()) || !stat.getCoach().equals(report.getCoach())) {
                    reportList.add(report);
                    
                    report = new UnfinishedRoundReport(stat.getSite(), stat.getCoach());
                }
                
                if(stat.getCourseType().isIndividual()) {
                    if(stat.isGive()) {
                        if(stat.getCourseStatus().isPrepare()) {
                            report.setGivePrepareAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isReady()) {
                            report.setGiveReadyAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isHold()) {
                            report.setGiveHoldAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isExpire()) {
                            report.setGiveExpireAmount(stat.getAmount());
                        }
                    } else {
                        if(stat.getCourseStatus().isPrepare()) {
                            report.setIndividualPrepareAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isReady()) {
                            report.setIndividualReadyAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isHold()) {
                            report.setIndividualHoldAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isExpire()) {
                            report.setIndividualExpireAmount(stat.getAmount());
                        }
                    }
                } else if(stat.getCourseType().isYear()) {
                    if(stat.getCourseStatus().isPrepare()) {
                        report.setYearPrepareAmount(stat.getAmount());
                    } else if(stat.getCourseStatus().isReady()) {
                        report.setYearReadyAmount(stat.getAmount());
                    } else if(stat.getCourseStatus().isHold()) {
                        report.setYearHoldAmount(stat.getAmount());
                    }
                }
                
                report.setTotalAmount(statMap.get(report.getSite()).get(report.getCoach()));
            }
            reportList.add(report);
        }
        
        return reportList;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listUnfinishedRoundStat(java.util.List, co.fitstart.entity.principal.Principal)
     */
    public List<UnfinishedRoundReport> listUnfinishedRoundStat(List<Site> sites, Principal coach) {
        
    	List<UnfinishedRoundStat> statByTypeAndStatusList = statByTypeAndStatus(sites, coach);
        
        Map<String, Map<String, Long>> statMap = stat(sites, coach);
        
        List<UnfinishedRoundReport> reportList = new ArrayList<>();
        
        if(!statByTypeAndStatusList.isEmpty()) {
            UnfinishedRoundReport report = new UnfinishedRoundReport(statByTypeAndStatusList.get(0).getSite(), statByTypeAndStatusList.get(0).getCoach());
            for(UnfinishedRoundStat stat : statByTypeAndStatusList) {
                if(!stat.getSite().equals(report.getSite()) || !stat.getCoach().equals(report.getCoach())) {
                    reportList.add(report);
                    
                    report = new UnfinishedRoundReport(stat.getSite(), stat.getCoach());
                }
                
                if(stat.getCourseType().isIndividual()) {
                    if(stat.isGive()) {
                        if(stat.getCourseStatus().isPrepare()) {
                            report.setGivePrepareAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isReady()) {
                            report.setGiveReadyAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isHold()) {
                            report.setGiveHoldAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isExpire()) {
                            report.setGiveExpireAmount(stat.getAmount());
                        }
                    } else {
                        if(stat.getCourseStatus().isPrepare()) {
                            report.setIndividualPrepareAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isReady()) {
                            report.setIndividualReadyAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isHold()) {
                            report.setIndividualHoldAmount(stat.getAmount());
                        } else if(stat.getCourseStatus().isExpire()) {
                            report.setIndividualExpireAmount(stat.getAmount());
                        }
                    }
                } else if(stat.getCourseType().isYear()) {
                    if(stat.getCourseStatus().isPrepare()) {
                        report.setYearPrepareAmount(stat.getAmount());
                    } else if(stat.getCourseStatus().isReady()) {
                        report.setYearReadyAmount(stat.getAmount());
                    } else if(stat.getCourseStatus().isHold()) {
                        report.setYearHoldAmount(stat.getAmount());
                    }
                }
                
                report.setTotalAmount(statMap.get(report.getSite()).get(report.getCoach()));
            }
            reportList.add(report);
        }
        
        return reportList;
    }

    /**
     * 
     * @param site
     * @param coach
     * @return
     */
    @SuppressWarnings("unchecked")
    private List<UnfinishedRoundStat> statByTypeAndStatus(Site site, Principal coach) {
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.UnfinishedRoundStat(site.name,coach.name,courseType,course.give,courseStatus,COUNT(DISTINCT student))"
                + " FROM CourseRound WHERE disabled = false"
                + " AND courseType IN (:courseTypes) AND course.experience = false"
                + " AND courseStatus IN (:courseStatuss)");
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseTypes", courseTypes);
        parameters.put("courseStatuss", new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE});
        
        if(site != null) {
            sentence.append(" AND site = :site");
            
            parameters.put("site", site);
        }
        
        if(coach != null) {
            sentence.append(" AND coach = :coach");
            
            parameters.put("coach", coach);
        }
        
        sentence.append(" GROUP BY site,coach,courseType,course.give,courseStatus"
                + " ORDER BY site,coach,courseType,course.give,courseStatus");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /**
     * 
     * @param sites
     * @param coach
     * @return
     */
    @SuppressWarnings("unchecked")
    private List<UnfinishedRoundStat> statByTypeAndStatus(List<Site> sites, Principal coach) {
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.UnfinishedRoundStat(site.name,coach.name,courseType,course.give,courseStatus,COUNT(DISTINCT student))"
                + " FROM CourseRound WHERE disabled = false"
                + " AND courseType IN (:courseTypes) AND course.experience = false"
                + " AND courseStatus IN (:courseStatuss)");
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseTypes", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        parameters.put("courseStatuss", new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE});
        
        sentence.append(" AND site IN (:sites)");
        parameters.put("sites", sites);
        
        if(coach != null) {
            sentence.append(" AND coach = :coach");
            
            parameters.put("coach", coach);
        }
        
        sentence.append(" GROUP BY site,coach,courseType,course.give,courseStatus"
                + " ORDER BY site,coach,courseType,course.give,courseStatus");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /**
     * 
     * @param site
     * @param coach
     * @return
     */
    @SuppressWarnings("unchecked")
    private Map<String, Map<String, Long>> stat(Site site, Principal coach) {
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.UnfinishedRoundStat(site.name,coach.name,COUNT(DISTINCT student))"
                + " FROM CourseRound WHERE disabled = false"
                + " AND courseType IN (:courseTypes) AND course.experience = false"
                + " AND courseStatus IN (:courseStatuss)");

        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseTypes", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        parameters.put("courseStatuss", new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE});
        
        if(site != null) {
            sentence.append(" AND site = :site");
            
            parameters.put("site", site);
        }
        
        if(coach != null) {
            sentence.append(" AND coach = :coach");
            
            parameters.put("coach", coach);
        }
        
        sentence.append(" GROUP BY site,coach"
                + " ORDER BY site,coach");
        
        List<UnfinishedRoundStat> statList = genericDaoSupport.searchForList(sentence.toString(), parameters);
        
        Map<String, Map<String, Long>> result = new HashMap<>();
        
        for(UnfinishedRoundStat unfinishedRoundStat : statList) {
            Map<String, Long> map = result.get(unfinishedRoundStat.getSite());
            if(map == null) {
                map = new HashMap<>();
            }
            map.put(unfinishedRoundStat.getCoach(), unfinishedRoundStat.getAmount());
            result.put(unfinishedRoundStat.getSite(), map);
        }
        
        return result;
    }

    /**
     * 
     * @param sites
     * @param coach
     * @return
     */
    @SuppressWarnings("unchecked")
    private Map<String, Map<String, Long>> stat(List<Site> sites, Principal coach) {
        StringBuffer sentence = new StringBuffer("SELECT new co.fitstart.admin.entity.report.UnfinishedRoundStat(site.name,coach.name,COUNT(DISTINCT student))"
                + " FROM CourseRound WHERE disabled = false"
                + " AND courseType IN (:courseTypes) AND course.experience = false"
                + " AND courseStatus IN (:courseStatuss)");

        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseTypes", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        parameters.put("courseStatuss", new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE});
        
        sentence.append(" AND site IN (:sites)");
        parameters.put("sites", sites);
        
        if(coach != null) {
            sentence.append(" AND coach = :coach");
            
            parameters.put("coach", coach);
        }
        
        sentence.append(" GROUP BY site,coach"
                + " ORDER BY site,coach");
        
        List<UnfinishedRoundStat> statList = genericDaoSupport.searchForList(sentence.toString(), parameters);
        
        Map<String, Map<String, Long>> result = new HashMap<>();
        
        for(UnfinishedRoundStat unfinishedRoundStat : statList) {
            Map<String, Long> map = result.get(unfinishedRoundStat.getSite());
            if(map == null) {
                map = new HashMap<>();
            }
            map.put(unfinishedRoundStat.getCoach(), unfinishedRoundStat.getAmount());
            result.put(unfinishedRoundStat.getSite(), map);
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listUnfinishedRound(co.fitstart.entity.site.Site, co.fitstart.entity.principal.Principal)
     */
    @SuppressWarnings("unchecked")
    public List<CourseRound> listUnfinishedRound(Site site, Principal coach) {
        StringBuffer sentence = new StringBuffer(" FROM CourseRound WHERE disabled = false"
                + " AND courseType IN (:courseTypes) AND course.experience = false"
                + " AND courseStatus IN (:courseStatuss)");
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseTypes", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        parameters.put("courseStatuss", new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE});
        
        if(site != null) {
            sentence.append(" AND site = :site");
            
            parameters.put("site", site);
        }
        
        if(coach != null) {
            sentence.append(" AND coach = :coach");
            
            parameters.put("coach", coach);
        }
        
        sentence.append(" ORDER BY site,coach,courseType,courseStatus");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listUnfinishedRound(java.util.List, co.fitstart.entity.principal.Principal)
     */
    @SuppressWarnings("unchecked")
    public List<CourseRound> listUnfinishedRound(List<Site> sites, Principal coach) {
        StringBuffer sentence = new StringBuffer(" FROM CourseRound WHERE disabled = false"
                + " AND courseType IN (:courseTypes) AND course.experience = false"
                + " AND courseStatus IN (:courseStatuss)");
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseTypes", new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        parameters.put("courseStatuss", new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE});
        
        sentence.append(" AND site IN (:sites)");
        parameters.put("sites", sites);
        
        if(coach != null) {
            sentence.append(" AND coach = :coach");
            
            parameters.put("coach", coach);
        }
        
        sentence.append(" ORDER BY site,coach,courseType,courseStatus");
        
        return genericDaoSupport.searchForList(sentence.toString(), parameters);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listTags(java.util.List)
     */
    public Map<Long, String> listTags(List<CourseRound> courseRounds) {
        Map<Long, String> tagMap = new HashMap<>();
        
        for(CourseRound courseRound : courseRounds) {
            StringBuffer tags = new StringBuffer();
            String tagIds = courseRound.getStudent().getTagIds();
            
            if(tagIds != null) {
                List<CoursewareTag> coursewareTags = fitnessTagDao.listCoursewareTag(this.getArrayTagIds(tagIds));
                
                for(CoursewareTag tag : coursewareTags) {
                    tags.append(tag.getName());
                    tags.append(",");
                }
            }
            
            if(tags.length() != 0) {
                tagMap.put(courseRound.getId(), tags.toString());
            }
        }
        
        return tagMap;
    }

    /**
     * 
     * @param tagIds
     * @return
     */
    private List<Long> getArrayTagIds(String tagIds) {
        List<Long> arrayTagIds = new ArrayList<>();
        if(tagIds != null) {
            for(String tagId : StringUtils.split(tagIds, ",")) {
                arrayTagIds.add(Long.parseLong(tagId));
            }
        }
        return arrayTagIds;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listDietary(java.util.List, co.fitstart.entity.principal.Principal)
     */
    public Map<Long, Integer> listDietary(List<Site> sites, Principal coach) {
        List<Map<String, Object>> list;
        
        if(coach == null) {
            list = reportDao.listDietary(CoursewareEditStatus.GENERATED, sites, Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE, RoundStatus.ACTIVATE}));
        } else {
            list = reportDao.listDietary(CoursewareEditStatus.GENERATED, sites, Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE, RoundStatus.ACTIVATE}), coach);
        }
        
        return convert(list);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listHomework(java.util.List, co.fitstart.entity.principal.Principal)
     */
    @SuppressWarnings("unchecked")
    public Map<Long, Integer> listHomework(List<Site> sites, Principal coach) {
        StringBuffer sentence = new StringBuffer("SELECT new Map(batch.courseRound.id AS id,MAX(batch.sorting) AS week) FROM Exercise"
                + " WHERE disabled=false AND batch.disabled=false AND batch.courseRound.disabled=false AND batch.editStatus = :generated"
                + " AND batch.courseRound.site IN (:sites) AND batch.courseRound.courseStatus IN (:courseStatus)"
                + " AND (homeworkFrequency IS NOT NULL OR lissType IS NOT NULL OR (otherLiss IS NOT NULL AND otherLiss != '')"
                + " OR (hit IS NOT NULL AND hit != '') OR (core IS NOT NULL AND core != '') OR (remark IS NOT NULL AND remark != ''))");
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("generated", CoursewareEditStatus.GENERATED);
        parameters.put("sites", sites);
        parameters.put("courseStatus", Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE, RoundStatus.ACTIVATE}));
        
        if(coach != null) {
            sentence.append(" AND batch.courseRound.coach = :coach");
            parameters.put("coach", coach);
        }
        
        sentence.append(" GROUP BY batch.courseRound.id");
        
        List<Map<String, Object>> list = genericDaoSupport.searchForList(sentence.toString(), parameters);
        
        
        return convert(list);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listPlannedSchedule(java.util.List, co.fitstart.entity.principal.Principal)
     */
    @SuppressWarnings("unchecked")
    public Map<Long, Integer> listPlannedSchedule(List<Site> sites, Principal coach) {
        StringBuffer sentence = new StringBuffer("SELECT new Map(batch.courseRound.id AS id,MAX(batch.sorting) AS week) FROM PlannedSchedule"
                + " WHERE disabled=false AND batch.disabled=false AND batch.courseRound.disabled=false AND batch.editStatus = :generated"
                + " AND batch.courseRound.site IN (:sites) AND batch.courseRound.courseStatus IN (:courseStatus)"
                + " AND content IS NOT NULL");
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("generated", CoursewareEditStatus.GENERATED);
        parameters.put("sites", sites);
        parameters.put("courseStatus", Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE, RoundStatus.ACTIVATE}));
        
        if(coach != null) {
            sentence.append(" AND batch.courseRound.coach = :coach");
            parameters.put("coach", coach);
        }
        
        sentence.append(" GROUP BY batch.courseRound.id");
        
        List<Map<String, Object>> list = genericDaoSupport.searchForList(sentence.toString(), parameters);
        
        return convert(list);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listCourse(java.util.List, co.fitstart.entity.principal.Principal)
     */
    public Map<Long, Long> listCourse(List<Site> sites, Principal coach) {
        StringBuffer sentence = new StringBuffer("SELECT cs.course_round_id AS id,Count(cs.id) AS session FROM course_schedule cs"
                + " JOIN (SELECT cr.id,max(cs.occur_date) occur_date FROM courseware c JOIN course_schedule cs ON c.course_schedule_id=cs.id"
                    + " JOIN course_round cr ON cs.course_round_id=cr.id"
                    + " WHERE c.is_disabled=false AND cs.is_disabled=false AND cr.is_disabled=false"
                    + " AND cr.course_status in (:prepare, :ready, :hold, :expire, :activate) AND cr.site_id in (:siteIds)");
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("prepare", RoundStatus.PREPARE.ordinal());
        parameters.put("ready", RoundStatus.READY.ordinal());
        parameters.put("hold", RoundStatus.HOLD.ordinal());
        parameters.put("expire", RoundStatus.EXPIRE.ordinal());
        parameters.put("activate", RoundStatus.ACTIVATE.ordinal());
        
        List<Long> siteIds = new ArrayList<>();
        for(Site site : sites) {
            siteIds.add(site.getId());
        }
        parameters.put("siteIds", siteIds);
        
        if(coach != null) {
            sentence.append(" AND cr.coach_id = :coachId");
            
            parameters.put("coachId", coach.getId());
        }
        
        sentence.append(" GROUP BY cr.id)t1"
                + " ON cs.course_round_id=t1.id AND cs.occur_date<=t1.occur_date"
                + " WHERE cs.is_disabled=false GROUP BY cs.course_round_id");
        
        List<Map<String, Object>> list = genericJdbcSupport.queryForList(sentence.toString(), parameters);
        
        Map<Long, Long> result = new HashMap<>();
        
        for(Map<String, Object> map : list) {
            result.put((Long)map.get("id"), (long)map.get("session"));
        }
        
        return result;
    }

    /**
     * 
     * @param list
     * @return
     */
    private Map<Long, Integer> convert(List<Map<String, Object>> list) {
        Map<Long, Integer> result = new HashMap<>();
        
        for(Map<String, Object> map : list) {
            result.put((Long)map.get("id"), (int)map.get("week"));
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listScheduleCancelStat(java.util.List, co.fitstart.entity.principal.Principal, java.util.Date, java.util.Date)
     */
    public Map<Long, Long> listScheduleCancelStat(List<Site> sites, Principal coach, Date begin, Date end) {
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        List<RoundStatus> courseStatuss = Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE});
        
        List<Map<String, Object>> mapList = new ArrayList<>();
        
        if(coach == null) {
            mapList = reportDao.listScheduleCancelStat(begin, end, courseTypes, courseStatuss, sites);
        } else {
            mapList = reportDao.listScheduleCancelStat(begin, end, courseTypes, courseStatuss, sites, coach);
        }
        
        Map<Long, Long> result = new HashMap<>();
        
        if(!mapList.isEmpty()) {
            for(Map<String, Object> map : mapList) {
                result.put((Long)map.get("id"), (Long)map.get("total"));
            }
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listLatestScheduleTime(java.util.List, co.fitstart.entity.principal.Principal)
     */
    public Map<Long, Date> listLatestScheduleTime(List<Site> sites, Principal coach) {
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        List<RoundStatus> courseStatuss = Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE});
        
        List<Map<String, Object>> mapList = new ArrayList<>();
        
        if(coach == null) {
            mapList = reportDao.listLatestScheduleTime(ScheduleStatus.FINISH, courseTypes, courseStatuss, sites);
        } else {
            mapList = reportDao.listLatestScheduleTime(ScheduleStatus.FINISH, courseTypes, courseStatuss, sites, coach);
        }
        
        Map<Long, Date> result = new HashMap<>();
        
        if(!mapList.isEmpty()) {
            for(Map<String, Object> map : mapList) {
                result.put((Long)map.get("id"), (Date)map.get("occurDate"));
            }
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listContrastPhoto(java.util.List, co.fitstart.entity.principal.Principal)
     */
    public Map<Long, Map<String, Object>> listContrastPhoto(List<Site> sites, Principal coach) {
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        List<RoundStatus> courseStatuss = Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE});
        
        List<Map<String, Object>> mapList = new ArrayList<>();
        
        if(coach == null) {
            //mapList = courseRoundResultDao.listContrastPhoto(courseTypes, courseStatuss, sites);
            mapList = reportDao.listContrastPhoto(courseTypes, courseStatuss, sites);
        } else {
            //mapList = courseRoundResultDao.listContrastPhoto(courseTypes, courseStatuss, sites, coach);
            mapList =reportDao.listContrastPhoto(courseTypes, courseStatuss, sites, coach);
        }
        
        Map<Long, Map<String, Object>> result = new HashMap<>();
        
        if(!mapList.isEmpty()) {
            for(Map<String, Object> map : mapList) {
                result.put((Long)map.get("id"), map);
            }
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listCourseRoundResult(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    public List<CourseRoundResult> listCourseRoundResult(Date begin, Date end, Site site) {
        List<CourseRoundResult> courseRoundResults = new ArrayList<>();
        
        if(site == null) {
            courseRoundResults = courseRoundResultDao.listCourseRoundResult(RoundStatus.FINISH, DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        } else {
            courseRoundResults = courseRoundResultDao.listCourseRoundResult(RoundStatus.FINISH, DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"), site);
        }
        
        return courseRoundResults;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listCourseRoundResult(java.util.Date, java.util.Date, java.util.List)
     */
    public List<CourseRoundResult> listCourseRoundResult(Date begin, Date end, List<Site> sites) {
        return courseRoundResultDao.listCourseRoundResult(RoundStatus.FINISH, DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"), sites);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listMultipleOrder(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    public Map<Long, Long> listMultipleOrder(Date begin, Date end, Site site) {
        List<User> users = new ArrayList<>();
        
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        
        if(site == null) {
            users = reportDao.listUser(courseTypes, RoundStatus.FINISH, DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        } else {
            users = reportDao.listUser(courseTypes, RoundStatus.FINISH, DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"), site);
        }
        
        return this.listMultipleOrder(users);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listMultipleOrder(java.util.Date, java.util.Date, java.util.List)
     */
    public Map<Long, Long> listMultipleOrder(Date begin, Date end, List<Site> sites) {
        List<User> users = reportDao.listUser(Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR}), RoundStatus.FINISH, DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"), sites);
        
        return this.listMultipleOrder(users);
    }

    /**
     * 
     * @param users
     * @return
     */
    private Map<Long, Long> listMultipleOrder(List<User> users) {
        Map<Long, Long> result = new HashMap<>();
        
        if(!users.isEmpty()) {
            List<Map<String, Object>> mapList = reportDao.countUserOrder(Arrays.asList(new OrderType[]{OrderType.INDIVIDUAL, OrderType.YEAR}), Arrays.asList(new OrderStatus[]{OrderStatus.PENDING, OrderStatus.PROCESSING, OrderStatus.COMPLETE, OrderStatus.HOLD}), PaymentStatus.PAID, users);
            
            if(!mapList.isEmpty()) {
                for(Map<String, Object> map : mapList) {
                    result.put((Long)map.get("id"), (Long)map.get("total"));
                }
            }
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countFinishedStudent(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    public Map<Long, Long> countFinishedStudent(Date begin, Date end, Site site) {
        Map<Long, Long> result = new HashMap<>();
        
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        Date beginTime = DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        Date endTime = DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss");
        
        if(site == null) {
            List<Map<String, Object>> mapList = reportDao.countFinishedStudent(courseTypes, RoundStatus.FINISH, beginTime, endTime);
            
            result = this.listToMap(mapList);
            
            result.put(0l, (long)reportDao.countAllFinishedStudent(courseTypes, RoundStatus.FINISH, beginTime, endTime));
        } else {
            int total = reportDao.countFinishedStudent(courseTypes, RoundStatus.FINISH, beginTime, endTime, site);
            
            if(total != 0) {
                result.put(site.getId(), (long)total);
            }
        }
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countFinishedStudent(java.util.Date, java.util.Date, java.util.List)
     */
    public Map<Long, Long> countFinishedStudent(Date begin, Date end, List<Site> sites) {
        Map<Long, Long> result = new HashMap<>();
        
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        Date beginTime = DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        Date endTime = DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss");
        
        List<Map<String, Object>> mapList = reportDao.countFinishedStudent(courseTypes, RoundStatus.FINISH, beginTime, endTime, sites);
        
        result = this.listToMap(mapList);
        
        result.put(0l, (long)reportDao.countAllFinishedStudent(courseTypes, RoundStatus.FINISH, beginTime, endTime, sites));
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countMultipleOrderBySite(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    public Map<Long, Long> countMultipleOrderBySite(Date begin, Date end, Site site) {
        Map<Long, Long> result = new HashMap<>();
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("individualCourse", CourseType.INDIVIDUAL.ordinal());
        parameters.put("yearCourse", CourseType.YEAR.ordinal());
        parameters.put("finish", RoundStatus.FINISH.ordinal());
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("individualOrder", OrderType.INDIVIDUAL.ordinal());
        parameters.put("yearOrder", OrderType.YEAR.ordinal());
        parameters.put("pending", OrderStatus.PENDING.ordinal());
        parameters.put("processing", OrderStatus.PROCESSING.ordinal());
        parameters.put("complete", OrderStatus.COMPLETE.ordinal());
        parameters.put("paid", PaymentStatus.PAID.ordinal());
        
        if(site == null) {
            List<Map<String, Object>> mapList = genericJdbcSupport.queryForList("SELECT temp.site_id id,COUNT(*) total"
                + " FROM (SELECT cr.site_id,cr.student_id FROM course_round cr"
                    + " JOIN course c ON cr.course_id=c.id"
                    + " JOIN orders o ON cr.student_id=o.student_id"
                    + " JOIN course oc ON o.course_id=oc.id"
                    + " WHERE cr.is_disabled=false AND cr.course_type IN (:individualCourse,:yearCourse) AND c.is_experience=false"
                    + " AND cr.course_status=:finish AND cr.finish_time BETWEEN :begin AND :end"
                    + " AND o.is_disabled=false AND oc.is_experience=false AND o.order_type IN (:individualOrder,:yearOrder)"
                    + " AND o.order_status IN (:pending,:processing,:complete) AND o.payment_status=:paid"
                    + " GROUP BY cr.site_id,cr.student_id HAVING COUNT(DISTINCT o.id)>1)temp GROUP BY temp.site_id", parameters);
            
            result = this.listToMap(mapList);
            
            result.put(0l, (long)genericJdbcSupport.queryForInt("SELECT COUNT(temp.student_id)"
                + " FROM (SELECT cr.student_id FROM course_round cr"
                    + " JOIN course c ON cr.course_id=c.id"
                    + " JOIN orders o ON cr.student_id=o.student_id"
                    + " JOIN course oc ON o.course_id=oc.id"
                    + " WHERE cr.is_disabled=false AND cr.course_type IN (:individualCourse,:yearCourse) AND c.is_experience=false"
                    + " AND cr.course_status=:finish AND cr.finish_time BETWEEN :begin AND :end"
                    + " AND o.is_disabled=false AND oc.is_experience=false AND o.order_type IN (:individualOrder,:yearOrder)"
                    + " AND o.order_status IN (:pending,:processing,:complete) AND o.payment_status=:paid"
                    + " GROUP BY cr.student_id HAVING COUNT(DISTINCT o.id)>1)temp", parameters));
        } else {
            parameters.put("siteId", site.getId());
            
            result.put(site.getId(), (long)genericJdbcSupport.queryForInt("SELECT COUNT(temp.student_id)"
                + " FROM (SELECT cr.student_id FROM course_round cr"
                    + " JOIN course c ON cr.course_id=c.id"
                    + " JOIN orders o ON cr.student_id=o.student_id"
                    + " JOIN course oc ON o.course_id=oc.id"
                    + " WHERE cr.is_disabled=false AND cr.course_type IN (:individualCourse,:yearCourse) AND c.is_experience=false"
                    + " AND cr.course_status=:finish AND cr.finish_time BETWEEN :begin AND :end AND cr.site_id = :siteId"
                    + " AND o.is_disabled=false AND oc.is_experience=false AND o.order_type IN (:individualOrder,:yearOrder)"
                    + " AND o.order_status IN (:pending,:processing,:complete) AND o.payment_status=:paid"
                    + " GROUP BY cr.student_id HAVING COUNT(DISTINCT o.id)>1)temp", parameters));
            
        }
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#countMultipleOrderBySite(java.util.Date, java.util.Date, java.util.List)
     */
    public Map<Long, Long> countMultipleOrderBySite(Date begin, Date end, List<Site> sites) {
        Map<Long, Long> result = new HashMap<>();
        
        List<Long> siteIds = new ArrayList<>();
        for(Site site : sites) {
            siteIds.add(site.getId());
        }
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("individualCourse", CourseType.INDIVIDUAL.ordinal());
        parameters.put("yearCourse", CourseType.YEAR.ordinal());
        parameters.put("finish", RoundStatus.FINISH.ordinal());
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("siteIds", siteIds);
        parameters.put("individualOrder", OrderType.INDIVIDUAL.ordinal());
        parameters.put("yearOrder", OrderType.YEAR.ordinal());
        parameters.put("pending", OrderStatus.PENDING.ordinal());
        parameters.put("processing", OrderStatus.PROCESSING.ordinal());
        parameters.put("complete", OrderStatus.COMPLETE.ordinal());
        parameters.put("paid", PaymentStatus.PAID.ordinal());
        
        
        List<Map<String, Object>> mapList = genericJdbcSupport.queryForList("SELECT temp.site_id id,COUNT(*) total"
                + " FROM (SELECT cr.site_id,cr.student_id FROM course_round cr"
                    + " JOIN course c ON cr.course_id=c.id"
                    + " JOIN orders o ON cr.student_id=o.student_id"
                    + " JOIN course oc ON o.course_id=oc.id"
                    + " WHERE cr.is_disabled=false AND cr.course_type IN (:individualCourse,:yearCourse) AND c.is_experience=false"
                    + " AND cr.course_status=:finish AND cr.finish_time BETWEEN :begin AND :end AND cr.site_id IN (:siteIds)"
                    + " AND o.is_disabled=false AND oc.is_experience=false AND o.order_type IN (:individualOrder,:yearOrder)"
                    + " AND o.order_status IN (:pending,:processing,:complete) AND o.payment_status=:paid"
                    + " GROUP BY cr.site_id,cr.student_id HAVING COUNT(DISTINCT o.id)>1)temp GROUP BY temp.site_id", parameters);
        
        result = this.listToMap(mapList);
        
        result.put(0l, (long)genericJdbcSupport.queryForInt("SELECT COUNT(temp.student_id)"
                + " FROM (SELECT cr.student_id FROM course_round cr"
                    + " JOIN course c ON cr.course_id=c.id"
                    + " JOIN orders o ON cr.student_id=o.student_id"
                    + " JOIN course oc ON o.course_id=oc.id"
                    + " WHERE cr.is_disabled=false AND cr.course_type IN (:individualCourse,:yearCourse) AND c.is_experience=false"
                    + " AND cr.course_status=:finish AND cr.finish_time BETWEEN :begin AND :end AND cr.site_id IN (:siteIds)"
                    + " AND o.is_disabled=false AND oc.is_experience=false AND o.order_type IN (:individualOrder,:yearOrder)"
                    + " AND o.order_status IN (:pending,:processing,:complete) AND o.payment_status=:paid"
                    + " GROUP BY cr.student_id HAVING COUNT(DISTINCT o.id)>1)temp", parameters));
        
        
        return result;
    }

    /**
     * 
     * @param mapList
     * @return
     */
    private Map<Long, Long> listToMap(List<Map<String, Object>> mapList) {
        Map<Long, Long> result = new HashMap<>();
        
        if(!mapList.isEmpty()) {
            for(Map<String, Object> map : mapList) {
                if(map.get("id") != null) {
                    result.put((long)map.get("id"), (long)map.get("total"));
                }
            }
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listGoal(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    public List<Goal> listGoal(Date begin, Date end, Site site) {
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        Date beginTime = DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        Date endTime = DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss");
        
        if(site == null) {
            return reportDao.listGoal(courseTypes, RoundStatus.FINISH, beginTime, endTime);
        } else {
            return reportDao.listGoal(courseTypes, RoundStatus.FINISH, beginTime, endTime, site);
        }
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listGoal(java.util.Date, java.util.Date, java.util.List)
     */
    public List<Goal> listGoal(Date begin, Date end, List<Site> sites) {
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        Date beginTime = DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        Date endTime = DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss");
        
        return reportDao.listGoal(courseTypes, RoundStatus.FINISH, beginTime, endTime, sites);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listTarget(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    public Map<Long, Map<String, Object>> listTarget(Date begin, Date end, Site site) {
        Map<Long, Map<String, Object>> result = new HashMap<>();
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("individual", CourseType.INDIVIDUAL.ordinal());
        parameters.put("year", CourseType.YEAR.ordinal());
        parameters.put("finish", RoundStatus.FINISH.ordinal());
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        List<Map<String, Object>> mapList;
        
        if(site == null) {
            mapList = genericJdbcSupport.queryForList("SELECT cr.id id,MIN(std.id) target,std.short_target shorttarget,std.long_target longtarget"
                    + " FROM course_round cr JOIN course c ON cr.course_id=c.id JOIN student_target_data std ON cr.id=std.course_round_id"
                    + " WHERE cr.is_disabled=false AND cr.course_type IN (:individual,:year) AND c.is_experience=false"
                    + " AND cr.course_status=:finish AND cr.finish_time BETWEEN :begin AND :end AND std.is_disabled = false GROUP BY cr.id", parameters);
        } else {
            parameters.put("siteId", site.getId());
            
            mapList = genericJdbcSupport.queryForList("SELECT cr.id id,MIN(std.id) target,std.short_target shorttarget,std.long_target longtarget"
                    + " FROM course_round cr JOIN course c ON cr.course_id=c.id JOIN student_target_data std ON cr.id=std.course_round_id"
                    + " WHERE cr.is_disabled=false AND cr.course_type IN (:individual,:year) AND c.is_experience=false"
                    + " AND cr.course_status=:finish AND cr.finish_time BETWEEN :begin AND :end AND cr.site_id = :siteId AND std.is_disabled = false GROUP BY cr.id", parameters);
        }
        
        if(!mapList.isEmpty()) {
            for(Map<String, Object> map : mapList) {
                result.put((Long)map.get("id"), map);
            }
        }
        
        return result;
    }

    @Override
    public Map<Long, Map<String, Object>> listTarget(Date begin, Date end, List<Site> sites) {
        Map<Long, Map<String, Object>> result = new HashMap<>();
        
        List<Long> siteIds = new ArrayList<>();
        for(Site site : sites) {
            siteIds.add(site.getId());
        }
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("individual", CourseType.INDIVIDUAL.ordinal());
        parameters.put("year", CourseType.YEAR.ordinal());
        parameters.put("finish", RoundStatus.FINISH.ordinal());
        parameters.put("begin", DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("end", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("siteIds", siteIds);
        
        List<Map<String, Object>> mapList = genericJdbcSupport.queryForList("SELECT cr.id id,MIN(std.id) target,std.short_target shorttarget,std.long_target longtarget"
                + " FROM course_round cr JOIN course c ON cr.course_id=c.id JOIN student_target_data std ON cr.id=std.course_round_id"
                + " WHERE cr.is_disabled=false AND cr.course_type IN (:individual,:year) AND c.is_experience=false"
                + " AND cr.course_status=:finish AND cr.finish_time BETWEEN :begin AND :end AND cr.site_id IN (:siteIds) AND std.is_disabled = false GROUP BY cr.id", parameters);
        
        if(!mapList.isEmpty()) {
            for(Map<String, Object> map : mapList) {
                result.put((Long)map.get("id"), map);
            }
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listUnfinishedRoundChange(java.util.List, co.fitstart.entity.principal.Principal)
     */
    public List<UnfinishedRoundChange> listUnfinishedRoundChange(List<Site> sites, Principal coach) {
        List<Long> siteIds = new ArrayList<>();
        for(Site site : sites) {
            siteIds.add(site.getId());
        }
        
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("individual", CourseType.INDIVIDUAL.ordinal());
        parameters.put("year", CourseType.YEAR.ordinal());
        parameters.put("ready", RoundStatus.READY.ordinal());
        parameters.put("hold", RoundStatus.HOLD.ordinal());
        parameters.put("expire", RoundStatus.EXPIRE.ordinal());
        parameters.put("siteIds", siteIds);
        
        StringBuffer sentence = new StringBuffer("SELECT temp.id,ir1.weight weight_before,ir2.weight weight_after,ir1.bfr bfr_before,ir2.bfr bfr_after"
                + " FROM (SELECT cr.id,MIN(ir.id) min_ir,MAX(ir.id) max_ir FROM course_round cr"
                    + " JOIN course c ON cr.course_id=c.id JOIN student s ON cr.student_id=s.id JOIN inbody_record ir ON ir.user_id=s.user_id"
                    + " WHERE cr.is_disabled = false AND cr.course_type IN (:individual,:year) AND c.is_experience = false"
                    + " AND cr.course_status IN (:ready,:hold,:expire) AND cr.site_id IN (:siteIds)");

        if(coach != null) {
            parameters.put("coachId", coach.getId());
            sentence.append(" AND cr.coach_id=:coachId");
        }
        
        sentence.append(" AND ir.record_time>=cr.effective_start AND ir.record_time<=NOW()"
                + " GROUP BY cr.id)temp JOIN inbody_record ir1 ON temp.min_ir=ir1.id JOIN inbody_record ir2 ON temp.max_ir=ir2.id");
        
        return genericJdbcSupport.queryForList(sentence.toString(), parameters, UnfinishedRoundChange.class);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listContrastPhoto(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    public Map<Long, Map<String, Object>> listContrastPhoto(Date begin, Date end, Site site) {
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        
        List<Map<String, Object>> mapList = new ArrayList<>();
        
        if(site == null) {
            mapList = reportDao.listContrastPhoto(courseTypes, RoundStatus.FINISH, DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        } else {
            mapList = reportDao.listContrastPhoto(courseTypes, RoundStatus.FINISH, DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"), site);
        }
        
        Map<Long, Map<String, Object>> result = new HashMap<>();
        
        if(!mapList.isEmpty()) {
            for(Map<String, Object> map : mapList) {
                result.put((Long)map.get("id"), map);
            }
        }
        
        return result;
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listContrastPhoto(java.util.Date, java.util.Date, java.util.List)
     */
    public Map<Long, Map<String, Object>> listContrastPhoto(Date begin, Date end, List<Site> sites) {
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        
        List<Map<String, Object>> mapList = reportDao.listContrastPhoto(courseTypes, RoundStatus.FINISH, DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"), DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"), sites);
        
        Map<Long, Map<String, Object>> result = new HashMap<>();
        
        if(!mapList.isEmpty()) {
            for(Map<String, Object> map : mapList) {
                result.put((Long)map.get("id"), map);
            }
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#mapCourseRoundResult(java.util.List, co.fitstart.entity.principal.Principal)
     */
    public Map<Long, CourseRoundResult> mapCourseRoundResult(List<Site> sites, Principal coach) {
        List<CourseRoundResult> courseRoundResults;
        if(coach == null) {
            courseRoundResults = courseRoundResultDao.list(
                    Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR}),
                    Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE, RoundStatus.ACTIVATE}), sites);
        } else {
            courseRoundResults = courseRoundResultDao.list(
                    Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR}),
                    Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE, RoundStatus.ACTIVATE}), sites, coach);
        }
        
        Map<Long, CourseRoundResult> result = new HashMap<>();
        
        for(CourseRoundResult courseRoundResult : courseRoundResults) {
            result.put(courseRoundResult.getCourseRound().getId(), courseRoundResult);
        }
        
        return result;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listStudentTargetData(java.util.Date, java.util.Date, co.fitstart.entity.site.Site)
     */
    public List<StudentTargetData> listStudentTargetData(Date begin, Date end, Site site) {
        List<StudentTargetData> targets;
        
        List<CourseType> courseTypes = Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR, CourseType.SPECIAL, CourseType.POSTURE});
        Date beginTime = DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        Date endTime = DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss");
        
        if(site == null) {
            targets = reportDao.listStudentTargetData(courseTypes, RoundStatus.FINISH, beginTime, endTime);
        } else {
            targets = reportDao.listStudentTargetData(courseTypes, RoundStatus.FINISH, beginTime, endTime, site);
        }
        
        return targets;
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listStudentTargetData(java.util.Date, java.util.Date, java.util.List)
     */
    public List<StudentTargetData> listStudentTargetData(Date begin, Date end, List<Site> sites) {
        return reportDao.listStudentTargetData(
                Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR}),
                RoundStatus.FINISH, DateUtils.parseDate(DateFormatUtils.format(begin, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"),
                DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"), sites);
    }

    /*
     * (non-Javadoc)
     * @see co.fitstart.admin.service.ReportService#listStudentTargetData(java.util.List, co.fitstart.entity.principal.Principal)
     */
    public List<StudentTargetData> listStudentTargetData(List<Site> sites, Principal coach) {
        if(coach == null) {
            return reportDao.listStudentTargetData(
                    Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR}),
                    Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE}),
                    sites);
        } else {
            return reportDao.listStudentTargetData(
                    Arrays.asList(new CourseType[]{CourseType.INDIVIDUAL, CourseType.YEAR}),
                    Arrays.asList(new RoundStatus[]{RoundStatus.PREPARE, RoundStatus.READY, RoundStatus.HOLD, RoundStatus.EXPIRE}),
                    sites, coach);
        }
    }
    
    /**
     * 
     */
    @SuppressWarnings("unchecked")
	public List<CourseRoundAttribute> listDelayRecord(Date now) {
    	
    	Map<String, Object> params = new HashMap<String, Object>();
    	
    	Calendar calendar = Calendar.getInstance();
    	calendar.setTime(now);
    	
    	calendar.set(Calendar.DAY_OF_YEAR, 1);
    	Date yearFirst = calendar.getTime();
    	
    	calendar.set(Calendar.DAY_OF_YEAR, calendar.getActualMaximum(Calendar.DAY_OF_YEAR));
    	Date yearLast = calendar.getTime();
    	
    	
    	params.put("start", DateFormatUtils.format(yearFirst, "yyyy-MM-dd") + " 00:00:00");
    	params.put("end", DateFormatUtils.format(yearLast, "yyyy-MM-dd") + " 23:59:59");
    	params.put("name", "delay_operate_time");
    	
    	return genericDaoSupport.searchForList("FROM CourseRoundAttribute WHERE name = :name AND value BETWEEN :start AND :end ORDER BY value DESC ", params);
    }
    
}